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

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

  •  1
  • EJ Lin  · 技术社区  · 1 年前

    我的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 回复  |  直到 1 年前
        1
  •  1
  •   Anuj Karki    1 年前

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

    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;
    
        2
  •  0
  •   Ralf    1 年前

    您可以将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;

        3
  •  0
  •   Ralf    1 年前

    这个应该有效:

    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;