我正在尝试使用最新的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
任何帮助或建议都将不胜感激。
提前感谢!
发布于 2018-06-09 02:27:01
最好的方法是使用窗口函数( Oracle terms分析函数)。如果您想返回领带(具有多个角色且具有相同开始日期的员工),则使用RANK()
或DENSE_RANK()
;如果您想要具有最近开始日期的任意角色,请使用ROW_NUMBER()
SELECT employee AS "E.EMPLOYEE #"
, description AS "Role"
, role_status AS "Status"
, role_start_date "Role Start"
FROM (
SELECT e.employee, lr.description, r.role_status, r.role_start_date
, RANK() OVER ( PARITION BY e.employee ORDER BY r.role_start_date DESC ) AS rn
FROM employees e INNER JOIN roles r
ON e.employee_id = r.employee_id
INNER JOIN lu_roles lr
ON lr.role_id = r.role_id
WHERE r.role_start_dt >= DATE'2017-12-03'
) WHERE rn = 1;
希望这能有所帮助。
发布于 2018-06-09 02:24:41
看看能不能帮上忙-
SELECT * FROM (
SELECT DISTINCT EMPLOYEE "E.EMPLOYEE #",
LR.DESCRIPTION "Role",
R.ROLE_STATUS "Status",
R.ROLE_START_DATE "Role Start",
DENSE_RANK() OVER (PARTITION BY EMPLOYEE ORDER BY ROLE_START_DATE DESC) AS RNK
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
) WHERE RNK = 1
发布于 2018-06-09 02:31:54
您可以结合使用ROW_NUMBER/RANK
和FETCH FIRST ... WITH TIES
(Oracle12c):
SELECT e.employee, lr.description, r.role_status, r.role_start_date
FROM employees e
JOIN roles r
ON e.employee_id = r.employee_id
JOIN lu_roles lr
ON lr.role_id = r.role_id
WHERE r.role_start_dt >= DATE'2017-12-03'
ORDER BY RANK() OVER (PARITION BY e.employee ORDER BY r.role_start_date DESC)
FETCH FIRST 1 ROW WITH TIES;
https://stackoverflow.com/questions/50766314
复制相似问题