我正在尝试使用最新的ROLE_START_DATE获取记录的详细信息。我已经尝试了多种方法,但都不能很好地工作。它最终总是提取所有数据,而不是特定的记录。
基本脚本:
SELECT DISTINCT EMPLOYEE "E.EMPLOYEE #",
LR.DESCRIPTION "Role",
R.ROLE_STATUS "Status",
R.ROLE_START_DATE "Role Start"
FROM EMPLOYEES E
JOIN ROLES R ON E.EMPLOYEE_ID = R.EMPLOYEE_ID
JOIN LU_ROLES LR ON R.ROLE_ID = LR.ROLE_ID
WHERE ROLE_START_DATE <= DATE '2017-12-03'
ORDER BY 1
结果:
Employee # | Role | Status | Role Start
23432 Associate Not Active 04/23/2011
23432 Manager Active 11/2/2012
54334 Analyst Resigned 10/15/2015
12311 Help Desk Not Active 05/12/2014
12311 Analyst Not Active 06/11/2015
12311 Supervisor Active 07/12/2016
修改为仅拉取具有最新日期的记录,但它不能按预期工作,并给出与上面完全相同的返回。
SELECT DISTINCT EMPLOYEE "E.EMPLOYEE #",
LR.DESCRIPTION,
R.ROLE_STATUS,
MAX(ROLE_START_DATE)
FROM EMPLOYEES E
JOIN ROLES R ON E.EMPLOYEE_ID = R.EMPLOYEE_ID
JOIN LU_ROLES LR ON R.ROLE_ID = LR.ROLE_ID
WHERE ROLE_START_DATE >= DATE '2017-12-03'
GROUP BY E.EMPLOYEE, LR.DESCRIPTION, R.ROLE_STATUS
ORDER BY 1
我想得到的是:
Employee # | Role | Status | Role Start
23432 Manager Active 11/2/2012
54334 Analyst Resigned 10/15/2015
12311 Supervisor Active 07/12/2016
任何帮助或建议都将不胜感激。
提前感谢!
https://stackoverflow.com/questions/50766314
复制相似问题