如何用SQL基于多日期关联订单与就诊表，获取就诊日期前的订单数据

原始表结构与数据 #

就诊日期表（命名为encounters） #

id  enc_id enc_dt         
1   5      06/11/20       
1   6      07/21/21        
1   7      09/15/21        
2   2      04/21/20                  
2   5      05/05/20       

订单日期表（命名为orders） #

id  enc_id ord_dt         
1   1      03/7/20        
1   2      04/14/20       
1   3      05/15/20        
1   4      05/30/20                  
1   5      06/12/20
1   6      07/21/21
1   7      09/16/21
1   8      10/20/21
1   9      10/31/21                    
2   1      04/15/20        
2   2      04/21/20       
2   3      04/30/20
2   4      05/02/20
2   5      05/05/20
2   6      05/10/20

需求说明 #

生成包含每笔就诊日期及之前所有订单数据的结果表，其中enc_key对应就诊表的enc_id，输出结构匹配你提供的示例。

解决方案 #

通过同用户关联+日期过滤的SQL语句实现，核心是对每个用户的就诊记录，匹配该用户下所有订单日期早于等于就诊日期的订单：

MySQL版本 #

SELECT 
    o.id,
    o.enc_id,
    o.ord_dt AS enc_dt,
    e.enc_id AS enc_key
FROM 
    encounters e
INNER JOIN 
    orders o ON o.id = e.id
WHERE 
    STR_TO_DATE(o.ord_dt, '%m/%d/%y') <= STR_TO_DATE(e.enc_dt, '%m/%d/%y')
ORDER BY 
    e.id, e.enc_id, o.enc_id;

SQL Server版本 #

SELECT 
    o.id,
    o.enc_id,
    o.ord_dt AS enc_dt,
    e.enc_id AS enc_key
FROM 
    encounters e
INNER JOIN 
    orders o ON o.id = e.id
WHERE 
    CONVERT(DATE, o.ord_dt, 101) <= CONVERT(DATE, e.enc_dt, 101)
ORDER BY 
    e.id, e.enc_id, o.enc_id;

PostgreSQL版本 #

SELECT 
    o.id,
    o.enc_id,
    o.ord_dt AS enc_dt,
    e.enc_id AS enc_key
FROM 
    encounters e
INNER JOIN 
    orders o ON o.id = e.id
WHERE 
    TO_DATE(o.ord_dt, 'MM/DD/YY') <= TO_DATE(e.enc_dt, 'MM/DD/YY')
ORDER BY 
    e.id, e.enc_id, o.enc_id;

逻辑说明 #

1. o.id = e.id：确保只关联同一用户的订单和就诊记录，避免跨用户匹配；
2. 日期转换函数：将字符串格式的日期转为数据库可识别的日期类型，避免字符串比较导致的逻辑错误；
3. 日期过滤条件：保留订单日期≤就诊日期的记录，满足“就诊日期之前及当天”的需求；
4. 排序：按用户id、就诊enc_id、订单enc_id排序，输出结构与预期一致。

内容的提问来源于stack exchange，提问作者llsabang