请求编写SQL查询重复申请者及申请状态的解决方案

1. 筛选曾失败后重考通过的申请者 #

需要先定位那些既有失败记录又有通过记录的申请者，然后返回他们的所有记录，排序时先按Year升序，同时把通过记录放在同一年份的最后（如果有的话）。

-- 先获取符合条件的申请者ID
WITH qualified_ids AS (
    SELECT Id
    FROM applicants
    GROUP BY Id
    HAVING MAX(Status) = '通过' AND MIN(Status) = '失败' -- 确保既有失败又有通过记录
)
SELECT a.Year, a.Status
FROM applicants a
JOIN qualified_ids q ON a.Id = q.Id
ORDER BY a.Year, CASE WHEN a.Status = '通过' THEN 1 ELSE 0 END;

• 逻辑说明：先用CTE筛选出同时存在Status为失败和通过的申请者Id，再关联原表取出所有记录；排序时通过CASE语句让通过记录的排序权重更高，从而排在同一年份的最后，同时整体按Year升序排列。

2. 筛选多次申请但始终失败的申请者 #

需要筛选出申请次数≥2，且所有记录Status都是失败的申请者。

WITH failed_only_ids AS (
    SELECT Id
    FROM applicants
    GROUP BY Id
    HAVING COUNT(*) >= 2 AND MAX(Status) = '失败' -- 申请次数≥2且全部为失败状态
)
SELECT a.Year, a.Status
FROM applicants a
JOIN failed_only_ids q ON a.Id = q.Id
ORDER BY a.Year;

• 逻辑说明：CTE筛选出申请次数≥2且所有Status均为失败的申请者Id，关联原表返回他们的所有记录，按Year排序。

3. 排除仅申请一次的申请者 #

直接筛选出申请次数≥2的申请者的所有记录即可。

WITH multi_apply_ids AS (
    SELECT Id
    FROM applicants
    GROUP BY Id
    HAVING COUNT(*) >= 2
)
SELECT a.Year, a.Status
FROM applicants a
JOIN multi_apply_ids q ON a.Id = q.Id
ORDER BY a.Id, a.Year;

• 逻辑说明：CTE获取申请次数≥2的申请者Id，关联原表返回所有记录，按申请者Id和Year排序，方便查看同一人的完整申请轨迹。

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