基于TABLE1生成含WANTCOL的TABLE2：SQL实现需求咨询

现有TABLE1数据 #

STUDENT DATE    SUBJECT
------------------------
1   2018-11-13  HISTORY
1   2018-11-14  HISTORY
1   2018-11-15  HISTORY
1   2018-11-15  ART
1   2018-11-12  ENGLISH
1   2018-11-14  ENGLISH
2   2018-11-14  ENGLISH
2   2018-11-13  ENGLISH
2   2018-11-12  ART
3   2018-11-12  HISTORY
3   2018-11-15  ENGLISH
3   2018-11-14  SCIENCE
3   2018-11-14  ART

需求规则 #

• 对每个STUDENT，先找到其SUBJECT = 'HISTORY'时的最小DATE
• 检查该学生是否存在SUBJECT != 'HISTORY'的记录，满足：
  • 该记录的DATE小于上述最小历史日期
  • 该记录的DATE大于（最小历史日期往前推6个月）
• 满足条件则WANTCOL = 1，否则为0；无历史记录的学生不纳入结果

预期TABLE2结果 #

STUDENT WANTCOL
----------------
   1        1
   3        0

尝试的代码 #

SELECT DISTINCT 
    STUDENT,
    CASE 
        WHEN (DATE < (SELECT MIN(DATE) 
                      FROM TABLE1 
                      WHERE SUBJECT = 'HISTORY' 
                      GROUP BY STUDENT) 
            AND SUBJECT != HISTORY PARITION OVER (STUDENT) 
            AND DATE >= DATEADD(MONTH, -6, SELECT MIN(DATE) FROM TABLE1 WHERE SUBJECT = 'HISTORY' GROUP BY STUDENT) FROM TABLE1 WHERE SUBJECT != HISTORY 
            THEN 1 
            ELSE 0 
    END AS WANTCOL
FROM 
    TABLE1

正确的SQL实现 #

思路 #

1. 先聚合每个有历史记录学生的最小历史日期
2. 通过EXISTS子查询判断该学生是否存在符合条件的非历史记录
3. 自动过滤无历史记录的学生，符合预期结果

通用SQL代码（适配多数数据库） #

SELECT 
    s.STUDENT,
    CASE 
        WHEN EXISTS (
            SELECT 1 
            FROM TABLE1 non_hist
            WHERE non_hist.STUDENT = s.STUDENT
              AND non_hist.SUBJECT != 'HISTORY'
              AND non_hist.DATE < s.min_history_date
              AND non_hist.DATE > DATEADD(MONTH, -6, s.min_history_date)
        ) THEN 1
        ELSE 0
    END AS WANTCOL
FROM (
    -- 筛选有历史记录的学生，计算其最小历史日期
    SELECT 
        STUDENT,
        MIN(DATE) AS min_history_date
    FROM TABLE1
    WHERE SUBJECT = 'HISTORY'
    GROUP BY STUDENT
) s

代码说明 #

• 子查询s先提取所有有历史记录的学生，并计算他们最早的历史日期
• 外部查询通过EXISTS验证：该学生是否存在日期在「最小历史日期前6个月到最小历史日期之间」的非历史记录
• 逻辑清晰，避免重复计算，同时自动排除无历史记录的学生（如学生2）

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