Question

我的文字是展示公司、雇员人数、最后名称、第一名称、当前工作和当前工资。

得出目前情况意味着有相关日期。

我如何展示该雇员目前的工作和工资?

段次页次

empno	lastname	firstname
111	james	lebron
222	bryant	kobe
333	jordan	michael
444	curry	stephen

优等

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

我在生效日期(有效日期)上使用了“彩虹”来获得目前最高水平的工作和工资。但它显示了过去的工作和工资。

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

Answer 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;

Answer 2

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

Answer 3

采用摘录于您的WHERE条款中的内容,该内容仅于最近的日期,因此只给你最新的记录。

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;

友情链接