我的sql脚本有问题,它要求我显示公司、员工编号、姓氏、名字、当前工作和当前工资。我知道,达到“当前”的东西意味着涉及到日期。
我想实现的是让它显示该员工的当前工作和工资。
>>这些是所使用的表格和视图<<
注意:
表中的信息不是真实的。我只是把它改了,这样你仍然可以得到我所指出的。
emp
桌子
+-------+----------+-----------+
| empno | lastname | firstname |
+-------+----------+-----------+
| 111 | james | lebron |
| 222 | bryant | kobe |
| 333 | jordan | michael |
| 444 | curry | stephen |
+-------+----------+-----------+
dept_job
看法
+------------+--------+-----------+------------+------------+
| company | empno | effdate | job | salary |
+------------+--------+-----------+------------+------------+
| lakers | 111 | 04/16/15 | assistant | 1500.00 |
| lakers | 111 | 02/02/16 | coach | 4000.00 |
| lakers | 222 | 07/22/17 | analyst | 3000.00 |
| bulls | 333 | 04/12/17 | secretary | 5000.00 |
| bulls | 333 | 07/28/18 | manager | 5000.00 |
| warriors | 444 | 10/04/19 | chef | 2000.00 |
+------------+--------+-----------+------------+------------+
我所做的是将Max()函数用于有效日期(effdate),以获得最新的工作和工资。但即使在那之后,它仍然显示了他过去的工作和薪水。我需要显示当前的工作和薪水。
SELECT z.company, em.empno, em.lastname, em.firstname, z.job, z.salary
FROM emp em
JOIN ( SELECT dj.company, dj.empno, dj.effdate, dj.job, dj.salary FROM dept_job dj
JOIN (SELECT company, empno, MAX(effdate) AS maxefffdate FROM dept_job
GROUP BY company, empno) pe ON dj.empno = pc.empno AND dj.effdate = pe.maxeffdate) z
ON em.empno = z.empno
ORDER BY company, empno;
错误的结果:
+------------+--------+----------+-----------+------------+------------+
| company | empno | lastname | firstname | job | salary |
+------------+--------+----------+-----------+------------+------------+
| lakers | 111 | james | lebron | assistant | 1500.00 |
| lakers | 111 | james | lebron | coach | 4000.00 |
| lakers | 222 | bryant | kobe | analyst | 3000.00 |
| bulls | 333 | jordan | michael | secretary | 5000.00 |
| bulls | 333 | jordan | michael | manager | 5000.00 |
| warriors | 444 | curry | stephen | chef | 2000.00 |
+------------+--------+----------+-----------+------------+------------+
我想要实现的目标
+------------+--------+----------+-----------+------------+------------+
| company | empno | lastname | firstname | job | salary |
+------------+--------+----------+-----------+------------+------------+
| lakers | 111 | james | lebron | coach | 4000.00 |
| lakers | 222 | bryant | kobe | analyst | 3000.00 |
| bulls | 333 | jordan | michael | manager | 5000.00 |
| warriors | 444 | curry | stephen | chef | 2000.00 |
+------------+--------+----------+-----------+------------+------------+