代码之家 › 专栏 › 技术社区 › EJ Lin

如何根据日期显示当前信息库

cloud subquery db2 join sql

EJ Lin · 技术社区 · 2 年前

我的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   |
+------------+--------+----------+-----------+------------+------------+

2 回复 | 直到 2 年前

Anuj Karki 2 年前

您可以从下面的查询中获得所需的输出:

SELECT dj.company
    ,em.empno
    ,em.lastname
    ,em.firstname
    ,dj.job
    ,dj.salary
FROM emp em
JOIN dept_job dj ON em.empno = dj.empno
JOIN (
    SELECT empno
        ,MAX(effdate) AS maxefffdate
    FROM dept_job
    GROUP BY empno
    ) z ON dj.empno = z.empno
    AND dj.effdate = z.maxefffdate
ORDER BY company
    ,empno;

Ralf 2 年前

您可以将OVER与PARTITION BY一起使用,并执行以下操作:

选择z。公司,em.empno,em.lastname,em.firstname,z。工作,z。薪水来自emp-em 加入(选择dj.company、dj.empno、MAX(dj.effedate)OVER( 由dj.company、dj.empno、dj.job、dj.salary分割 )AS最大有效日期,dj.job,dj.salary 来自dept_job dj)z ON em.empno=z.empno 公司订单,empno;

Ralf 2 年前

这个应该有效:

SELECT dj.company, e.empno, e.lastname, e.firstname, dj.job
    FROM emp e
    JOIN dept_job dj ON e.empno = dj.empno
    WHERE dj.effdate = (
      SELECT MAX(effdate)
      FROM dept_job
      WHERE empno = e.empno
    )
    ORDER BY e.empno;