SQL查询符合条件的外键：需含状态a-d（非空日期）及e（空日期）

最优实现方案（直接返回结果） #

使用GROUP BY结合HAVING子句，直接在分组后过滤满足所有条件的FK，无需手动筛选：

SELECT FK
FROM t1
GROUP BY FK
HAVING 
    SUM(CASE WHEN Status = 'a' AND Date IS NOT NULL THEN 1 ELSE 0 END) > 0
    AND SUM(CASE WHEN Status = 'b' AND Date IS NOT NULL THEN 1 ELSE 0 END) > 0
    AND SUM(CASE WHEN Status = 'c' AND Date IS NOT NULL THEN 1 ELSE 0 END) > 0
    AND SUM(CASE WHEN Status = 'd' AND Date IS NOT NULL THEN 1 ELSE 0 END) > 0
    AND SUM(CASE WHEN Status = 'e' AND Date IS NULL THEN 1 ELSE 0 END) > 0;

代码解释 #

• GROUP BY FK：按外键分组，将每个FK的所有记录聚合在一起
• 每个SUM(CASE...)：统计当前FK下符合对应状态条件的记录数量，若数量大于0，说明该FK存在满足此条件的记录
• HAVING子句：仅保留所有5个条件均满足的FK，直接输出目标结果

替代方案（使用EXISTS子查询） #

若偏好更直观的条件判断逻辑，也可采用EXISTS子查询实现：

SELECT DISTINCT FK
FROM t1 t_main
WHERE
    EXISTS (SELECT 1 FROM t1 WHERE FK = t_main.FK AND Status = 'a' AND Date IS NOT NULL)
    AND EXISTS (SELECT 1 FROM t1 WHERE FK = t_main.FK AND Status = 'b' AND Date IS NOT NULL)
    AND EXISTS (SELECT 1 FROM t1 WHERE FK = t_main.FK AND Status = 'c' AND Date IS NOT NULL)
    AND EXISTS (SELECT 1 FROM t1 WHERE FK = t_main.FK AND Status = 'd' AND Date IS NOT NULL)
    AND EXISTS (SELECT 1 FROM t1 WHERE FK = t_main.FK AND Status = 'e' AND Date IS NULL);

与原代码的对比 #

原代码通过计算sum_status间接判断条件，需手动筛选数值，存在明显缺陷：

1. 逻辑不直观：需要牢记sum_status的目标阈值（如示例中的104），业务规则变更时需同步调整数值计算逻辑
2. 易出错：若同一FK下存在重复的符合条件记录，sum_status会超出阈值，手动筛选易出现误判

上述两种方案均通过直接条件判断筛选结果，逻辑清晰、结果准确，无需后续手动处理。

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