SQL如何筛选参加2001年马拉松但未参加2000年赛事的运动员姓名

解决方案 #

假设你的表名为marathon，以下是几种可正确得到结果的写法：

写法1：NOT EXISTS（最稳妥，无NULL值陷阱） #

SELECT DISTINCT athlete
FROM marathon m1
WHERE year = 2001
AND NOT EXISTS (
    SELECT 1
    FROM marathon m2
    WHERE m2.athlete = m1.athlete
    AND m2.year = 2000
);

逻辑：先筛选出所有2001年参赛的运动员，再排除掉同时存在2000年参赛记录的运动员。

写法2：LEFT JOIN + 空值判断 #

SELECT DISTINCT m1.athlete
FROM marathon m1
LEFT JOIN marathon m2 
    ON m1.athlete = m2.athlete 
    AND m2.year = 2000
WHERE m1.year = 2001
AND m2.athlete IS NULL;

逻辑：将2001年参赛的运动员和2000年的参赛记录做关联，关联不上的就是没有2000年参赛记录的选手。

写法3：NOT IN（注意使用前提：athlete字段无NULL值） #

SELECT DISTINCT athlete
FROM marathon
WHERE year = 2001
AND athlete NOT IN (
    SELECT athlete
    FROM marathon
    WHERE year = 2000
);

如果之前用NOT IN出错，大概率是子查询返回的结果里存在NULL值，NOT IN碰到NULL会返回未知，最终查不到任何结果。

写法4：聚合统计写法 #

SELECT athlete
FROM marathon
WHERE year IN (2000,2001)
GROUP BY athlete
HAVING MAX(year) = 2001 AND MIN(year) = 2001;

逻辑：统计有这两年参赛记录的选手，只保留参赛年份只有2001的选手。

针对你提供的样例数据，以上所有查询的返回结果都是Johnson，符合预期。

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