SQL技术问询：如何构造符合第二重复判定准则的查询语句

逻辑梳理 #

第二准则的核心是：同一员工（employee_id）的同一项目（project_id）下存在不同状态（status），但排除仅包含REJECTED和REQUIRED两种状态的情况。

实现SQL #

SELECT
    employee_id,
    project_id,
    GROUP_CONCAT(DISTINCT status ORDER BY status) AS status_list,
    COUNT(DISTINCT status) AS distinct_status_count
FROM employee_projects
GROUP BY employee_id, project_id
HAVING
    -- 组内存在至少2种不同状态
    distinct_status_count >= 2
    -- 排除仅包含REJECTED和REQUIRED的情况
    AND NOT (
        SUM(CASE WHEN status = 'REJECTED' THEN 1 ELSE 0 END) > 0
        AND SUM(CASE WHEN status = 'REQUIRED' THEN 1 ELSE 0 END) > 0
        AND distinct_status_count = 2
    );

语句解释 #

1. 分组与基础统计：按employee_id和project_id分组，用COUNT(DISTINCT status)统计组内不同状态的数量，GROUP_CONCAT直观展示所有状态组合。
2. 筛选条件：
   • distinct_status_count >= 2：确保组内存在不同状态的记录；
   • NOT (...)：精准排除组内恰好只有REJECTED和REQUIRED两种状态的情况——通过SUM(CASE...)检查这两个状态是否都存在，且状态总数严格为2。

结果验证 #

针对你的示例数据，该查询会返回：

这正好对应你示例中符合第二准则的行6和行9。而emp2-proj2（状态为REJECTED和REQUIRED）会被排除，完全符合规则要求。

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