我已经创建了两个表:
create table DEPARTMENTS
(
dept_id number,
dept varchar2(50) not null,
mgr_id number,
location varchar2(50),
constraint pk_departments primary key (dept_id)
);
create table EMPLOYEES
(
emp_id number,
fname varchar2(50) not null,
lname varchar2(50) not null,
email varchar2(50),
phone number,
hired date,
job_id varchar(20),
salary number(7,2),
commission_pct number(4,2),
dept_id number,
grade number,
constraint pk_employees primary key (emp_id),
constraint fk_employees_dept_id foreign key (dept_id)
references DEPARTMENTS (dept_id)
constraint fk_employees_grade foreign key (grade)
references SALGRADE (grade)
);
假设mgr_id在表departments中,employee在employees表中,并且它们通过dept_id链接,我如何获得收入高于其经理的员工的姓名?
我发现'self-join‘是必要的。但是这种情况有点困难,因为需要引用dept_id来引用mgr_id,然后将其与emp_id进行比较,然后创建连接?
发布于 2020-08-02 10:05:29
为了更好地清晰和自我理解,使用ANSI连接:
SELECT * FROM
(SELECT E.SALARY AS MANAGER_SALARY, D.DEPT_ID AS DEPT_ID
FROM EMPLOYEES E
INNER JOIN
DEPARTMENTS D
ON E.DEPT_ID = D.DEPT_ID)
MGR_DET
INNER JOIN
EMPLOYEES EMP
ON MGR_DET.DEPT_ID = EMP.DEPT_ID
where EMP.SALARY > MGR_DET.MANAGER_SALARY;
发布于 2020-08-02 11:27:13
您可以使用三个连接来实现这一点。从employees
表开始,匹配departments
以获取管理器。然后使用管理器匹配回employees
。
这看起来像这样:
select e.*, em.salary -- or whatever columns you want
from employees e join
departments d
on d.dept_id = e.dept_id join
employees em
on em.emp_id = d.mgr_id
where e.salary > em.salary
发布于 2020-08-02 09:15:24
使用隐式连接回答:
SELECT E1.* FROM EMPLOYEES E1, DEPARTMENT D, EMPLOYEES E2
WHERE E1.DEPT_ID = D.DEPT_ID AND D.DEPT_ID = E2.DEPT_ID AND D.MGR_ID = E2.MGR_ID
AND E1.SALARY >= E2.SALARY;
这里的employee E1是薪水高于部门经理的雇员。为此,我们连接employee表E1和department表D以获取dept_id
,然后获取mgr_id
。现在,下一步是查找ID与部门mgr_id
相同的员工。因此,我们再次将其与emp_id
上的另一个employee表E2联接。最后,我们比较了员工e1和经理(employee e2)的工资。
使用显式连接来回答:
SELECT E1.* FROM EMPLOYEES E1 JOIN DEPARTMENT D ON E1.DEPT_ID = D.DEPT_ID
JOIN EMPLOYEES E2 ON (D.DEPT_ID = E2.DEPT_ID AND D.MGR_ID = E2.MGR_ID)
WHERE E1.SALARY >= E2.SALARY;
https://stackoverflow.com/questions/63214208
复制相似问题