SQL查询:获取员工病假日期前最近一次出差到达地点的实现方法
员工病假关联对应出差地点的SQL实现
现有数据表说明
- 员工病假表
存储字段:员工、病假呼叫日期
示例数据:- 员工A:病假日期为
2020/1/1、2020/8/2 - 员工B:病假日期为
2020/2/5、2020/12/20
- 员工A:病假日期为
- 员工出差记录表
存储字段:员工、出差地点、抵达日期
示例数据:- 员工A:纽约(
2019/10/1)、波士顿(2019/12/15)、迈阿密(2020/6/6) - 员工B:洛杉矶(
2020/1/1)、圣迭戈(2020/10/6)
- 员工A:纽约(
业务目标
关联得到每个员工每次请病假时所在的出差地点,最终输出三个维度:员工、病假日期、对应地点,预期匹配结果为:
- 员工A:
2020/1/1→波士顿,2020/8/2→迈阿密 - 员工B:
2020/2/5→洛杉矶,2020/12/20→圣迭戈
核心实现难点
需要精准匹配同员工下,早于病假日期的最近一次抵达日期对应的出差地点,而非任意一条早于病假的出差记录
可落地方案
方案一:通用窗口函数实现(适配MySQL8.0+、PostgreSQL、Oracle等主流数据库)
通过窗口函数先为每个员工的出差抵达记录按时间倒序排名,再关联病假表匹配最近的一条符合时间条件的记录:
WITH ranked_arrival AS ( SELECT 员工, 出差地点, 抵达日期, -- 同员工的所有抵达记录,越近的排名数字越小 ROW_NUMBER() OVER (PARTITION BY 员工 ORDER BY 抵达日期 DESC) AS arr_rank FROM 员工出差记录表 ) SELECT s.员工, s.病假呼叫日期, a.出差地点 AS 对应地点 FROM 员工病假表 s LEFT JOIN ranked_arrival a ON s.员工 = a.员工 AND a.抵达日期 <= s.病假呼叫日期 -- 过滤出每个病假条目对应的排名最靠前(即最近)的抵达记录 QUALIFY arr_rank = MIN(arr_rank) OVER (PARTITION BY s.员工, s.病假呼叫日期);
方案二:兼容低版本数据库的实现(无窗口函数支持也可运行)
如果使用的数据库版本较低、不支持窗口函数的QUALIFY语法,可通过先聚合取最大抵达日期再关联的方式实现:
-- 先计算每个员工每次病假对应的最近一次抵达日期 WITH latest_arrival AS ( SELECT s.员工, s.病假呼叫日期, MAX(a.抵达日期) AS 最近抵达日期 FROM 员工病假表 s LEFT JOIN 员工出差记录表 a ON s.员工 = a.员工 AND a.抵达日期 <= s.病假呼叫日期 GROUP BY s.员工, s.病假呼叫日期 ) SELECT la.员工, la.病假呼叫日期, a.出差地点 AS 对应地点 FROM latest_arrival la INNER JOIN 员工出差记录表 a ON la.员工 = a.员工 AND la.最近抵达日期 = a.抵达日期;
内容的提问来源于stack exchange,提问作者A Boat
相关产品推荐
相关产品推荐

