select . . .
from (select e.*, m.hiredate as mgr_hiredate,
min(salar) over (partition by job) as min_salar
from emp e left join
emp em
on e.mgr = m.empno
) em
where hiredate < mgr_hiredate and salar = min_salar;
SELECT EMPNO, ENAME
FROM EMP e
INNER JOIN EMP m ON e.MGR = m.EMPNO
INNER JOIN (
SELECT JOB, MIN(SALAR) as MINSALAR
FROM EMP
GROUP BY JOB
) s ON s.JOB = e.JOB
WHERE m.HIREDATE > e.HIREDATE AND e.SALAR = s.MINSALAR
2条答案
按热度按时间zpgglvta1#
使用窗口功能:
8xiog9wr2#
你可以加入
EMP
添加每个员工经理的详细信息(我们对他的经理很感兴趣)HIREDATE
). 以后WHERE
你可以查一下经理的HIREDATE
晚于相应员工的。您还可以预先计算每个工作的所有最低工资,然后将此信息加入到每个员工中,然后可以检查员工的工资是否等于最低工资。