我有一个状态历史记录表,其中还包括将来的过时记录。
示例:employee_jobs
id | employee_id | division_id | department_id | job_id | effective_date
1 100 1 1 1 2015-01-01
2 100 1 1 2 2016-01-01
3 100 1 2 4 2017-01-01
4 200 1 3 5 2016-01-01
5 300 1 3 6 2015-01-01
6 300 1 3 7 2016-05-25
我需要一个预成型的SQL,该SQL在给定日期时将显示给定的employee_id的当前活动记录:示例日期= 2016-08-15
结果集应为:
id | employee_id | division_id | department_id | job_id | effective_date
2 100 1 1 2 2016-01-01
4 200 1 3 5 2016-01-01
6 300 1 3 7 2016-05-25
我猜你要为每个员工有最新的记录effective_date
与约束
(effective_date
必须小于或等于给定的日期)
SELECT
*
FROM
(
SELECT
*,
IF(@sameEmployee = employee_id, @rn := @rn + 1,
IF(@sameEmployee := employee_id, @rn := 1, @rn := 1)
) AS row_number
FROM employee_jobs
CROSS JOIN (SELECT @sameEmployee := 0, @rn := 1) var
WHERE effective_date <= '2016-08-15'
ORDER BY employee_id, effective_date DESC
) AS t
WHERE t.row_number = 1
ORDER BY t.employee_id
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句