如何将这些数据从其他表插入到该表中

Question

表XX_EMPLOYEES

CREATE TABLE XX_EMPLOYEES
( EMP_ID NUMBER NOT NULL, EMP_FIRST_NAME VARCHAR2(250) NOT NULL, EMP_MIDDLE_NAME VARCHAR2(250) NOT NULL, EMP_LAST_NAME VARCHAR2(250) NOT NULL, Hired_Date DATE NOT NULL, Country VARCHAR2(250) NOT NULL, Salary NUMBER NOT NULL );
INSERT ALL
INTO XX_EMPLOYEES (EMP_ID, EMP_FIRST_NAME, EMP_MIDDLE_NAME, EMP_LAST_NAME, Hired_Date, Country, Salary) VALUES (1,'Tomm','Jef','Adam','01-Jan-2016','JORDAN',1000) INTO XX_EMPLOYEES (EMP_ID, EMP_FIRST_NAME, EMP_MIDDLE_NAME, EMP_LAST_NAME, Hired_Date, Country, Salary) VALUES (2,'Mohammed','Ahmed','Mahmoud','15-Jul-2009','UAE',900) INTO XX_EMPLOYEES (EMP_ID, EMP_FIRST_NAME, EMP_MIDDLE_NAME, EMP_LAST_NAME, Hired_Date, Country, Salary) VALUES (4,'Ali','Ahmad','Mahmoud','07-Jul-2000','UK',1200) INTO XX_EMPLOYEES (EMP_ID, EMP_FIRST_NAME, EMP_MIDDLE_NAME, EMP_LAST_NAME, Hired_Date, Country, Salary) VALUES (10,'Basel','Jamal','Saeed','10-Apr-2001','UAE',1000)
SELECT * FROM dual;

表作业

CREATE TABLE Jobs
(
JOB_ID NUMBER NOT NULL,
JOB_Description VARCHAR2(250) NOT NULL
);
INSERT ALL
INTO Jobs (Job_ID, Job_Description) VALUES (1, 'Accountant')
INTO Jobs (Job_ID, Job_Description) VALUES (2, 'General Manager')
INTO Jobs (Job_ID, Job_Description) VALUES (3, 'Administration')
INTO Jobs (Job_ID, Job_Description) VALUES (4, 'Senior Accountant')
SELECT * FROM dual;

桌面专业

CREATE TABLE  PROFESSION
(     EMP_ID  NUMBER NOT NULL, 
JOB_ID  NUMBER NOT NULL, 
MANAGER_ID NUMBER, 
EFFECTIVE_FROM DATE NOT NULL, 
EFFECTIVE_TO DATE NOT NULL, 
CONSTRAINT RESTRICT CHECK (EFFECTIVE_FROM < EFFECTIVE_TO)
)

现在，我如何使用所有3个表将这些数据添加到职业表中？

(Tomm, General Manager,null,01-Jan-2000,null)
(Mohammed, Senior Accountant, Tomm,01-Jan-2010, Null)
(Ali, Administration, Tomm,01-Jan-2000,Null)
(Basel, Accountant, Mohammed,01-Mar-2007,Null)

桌子行业有以下限制：-

Staff、Effective_from和Effective_to是必填字段，必须输入。

Effective_from必须小于Effective_to。

对于同一员工，其在生效日内不得同时有两项记录。

尝试过这个，但仍然无法获得

INSERT INTO PROFESSION(EMP_ID,JOB_ID,STAFF)
VALUES ((SELECT EMP_ID FROM XX_EMPLOYEES WHERE EMP_ID=1003),(SELECT JOB_ID FROM JOBS WHERE JOB_ID=102),(SELECT EMP_FIRST_NAME FROM XX_EMPLOYEES WHERE EMP_ID=1003))

Answer 1

要插入行，您必须通过case表达式连接xx_employees和jobs，实际上是将人员与工作连接起来。

SQL> INSERT INTO profession (emp_id,
2                          job_id,
3                          manager_id,
4                          effective_from,
5                          effective_to)
6     SELECT e.emp_id,
7            j.job_id,
8            --
9            CASE                        -- this CASE expression sets the manager
10               WHEN e.emp_id = 1 THEN NULL
11               WHEN e.emp_id = 2 THEN 1
12               WHEN e.emp_id = 4 THEN 1
13               WHEN e.emp_id = 10 THEN 2
14            END manager_id,
15            --
16            DATE '2000-01-01',
17            DATE '3000-01-01' -- the "TO" date can't be null, so it is set to far future
18       FROM xx_employees e
19            JOIN jobs j
20               ON j.job_id =              -- CASE expressions says who does what
21                  CASE
22                     WHEN e.emp_id = 1 THEN 2
23                     WHEN e.emp_id = 2 THEN 4
24                     WHEN e.emp_id = 4 THEN 1
25                     WHEN e.emp_id = 10 THEN 1
26                  END;
4 rows created.

结果是几个表再次连接；请注意，您必须使用xx_employees两次：e别名用于"；雇员"；，而m别名用于管理者：

SQL>   SELECT e.emp_first_name,
2           j.job_description,
3           m.emp_first_name manager,
4           p.effective_from,
5           p.effective_to
6      FROM profession p
7           JOIN xx_employees e ON e.emp_id = p.emp_id
8           LEFT JOIN xx_employees m ON m.emp_id = p.manager_id
9           JOIN jobs j ON j.job_id = p.job_id
10  ORDER BY p.emp_id;
EMP_FIRST_NAME  JOB_DESCRIPTION      MANAGER    EFFECTIVE_F EFFECTIVE_T
--------------- -------------------- ---------- ----------- -----------
Tomm            General Manager                 01-jan-2000 01-jan-3000
Mohammed        Senior Accountant    Tomm       01-jan-2000 01-jan-3000
Ali             Accountant           Tomm       01-jan-2000 01-jan-3000
Basel           Accountant           Mohammed   01-jan-2000 01-jan-3000
SQL>