SQL实现查询两个日期字段之间的所有日期并逐行展示

现有存储患者数据的PatientData表，包含PatientNumber（患者编号）、AdmitDate（入院日期）、DischargeDate（出院日期）三个字段，需要查询每名患者从入院到出院区间内的所有日期，每个日期单独生成一行记录。

现有表样例数据 #

期望返回结果 #

核心逻辑是生成覆盖所有患者住院周期的连续日期序列，再和患者表做关联，筛选出每个患者住院区间内的日期即可，不同数据库的具体写法有区别：

• 不需要额外建表的递归CTE写法，适配支持CTE的主流数据库版本
• 老版本数据库可提前建一张预存连续日期的日历辅助表，关联逻辑和下面一致，性能更稳定

MySQL 8.0+ 写法 #

WITH RECURSIVE date_seq AS (
    -- 取表中最小入院日期作为序列起点
    SELECT MIN(AdmitDate) AS seq_date FROM PatientData
    UNION ALL
    -- 按天递归生成日期，直到覆盖最大出院日期
    SELECT DATE_ADD(seq_date, INTERVAL 1 DAY)
    FROM date_seq
    WHERE seq_date < (SELECT MAX(DischargeDate) FROM PatientData)
)
SELECT 
    p.PatientNumber,
    p.AdmitDate,
    p.DischargeDate,
    d.seq_date AS DateOnLocation
FROM PatientData p
JOIN date_seq d 
    ON d.seq_date BETWEEN p.AdmitDate AND p.DischargeDate
ORDER BY p.PatientNumber, d.seq_date;

SQL Server 写法 #

WITH date_seq AS (
    SELECT MIN(AdmitDate) AS seq_date FROM PatientData
    UNION ALL
    SELECT DATEADD(DAY, 1, seq_date)
    FROM date_seq
    WHERE seq_date < (SELECT MAX(DischargeDate) FROM PatientData)
)
SELECT 
    p.PatientNumber,
    p.AdmitDate,
    p.DischargeDate,
    d.seq_date AS DateOnLocation
FROM PatientData p
INNER JOIN date_seq d 
    ON d.seq_date BETWEEN p.AdmitDate AND p.DischargeDate
ORDER BY p.PatientNumber, d.seq_date
-- 关闭递归层级限制，避免住院周期长时报错
OPTION (MAXRECURSION 0);

PostgreSQL 写法 #

PostgreSQL自带序列生成函数，写法更简洁：

SELECT 
    p.PatientNumber,
    p.AdmitDate,
    p.DischargeDate,
    d.seq_date AS DateOnLocation
FROM PatientData p
CROSS JOIN generate_series(
    p.AdmitDate, 
    p.DischargeDate, 
    INTERVAL '1 day'
) AS d(seq_date)
ORDER BY p.PatientNumber, d.seq_date;

内容的提问来源于stack exchange，提问作者Stephen Bradley Archer