Netezza SQL：修正NOT IN查询以筛选仅含指定年份的学生

现有数据表my_data：

student_id years
1         123  2010
2         123  2011
3         123  2012
4         124  2010
5         124  2011
6         124  2012
7         124  2013
8         125  2010
9         125  2011
10        125  2012
11        125  2013
12        125  2014

需求：仅筛选出所有年份恰好为2010、2011、2012、2013的学生，即仅student_id=124符合条件。

原查询使用NOT IN实现，但错误返回了student_id=123和124的记录：

SELECT student_id, years
FROM my_data
WHERE student_id NOT IN (
    SELECT student_id
    FROM my_data
    WHERE years NOT IN (2010, 2011, 2012, 2013)
)

错误结果：

student_id years
1        123  2010
2        123  2011
3        123  2012
4        124  2010
5        124  2011
6        124  2012
7        124  2013

修正方案 #

原查询仅排除了存在超出目标年份的学生（如125），但没有排除年份不全的学生（如123缺少2013年）。需要同时满足两个条件：

1. 学生的所有年份都属于{2010,2011,2012,2013}
2. 学生恰好拥有这四个年份

方法一：通过聚合函数验证年份范围和数量

SELECT student_id, years
FROM my_data
WHERE student_id IN (
    SELECT student_id
    FROM my_data
    GROUP BY student_id
    HAVING COUNT(DISTINCT years) = 4 
       AND MIN(years) = 2010 
       AND MAX(years) = 2013
)

逻辑：分组后，COUNT(DISTINCT years)=4确保学生有4个不同年份，MIN(years)=2010和MAX(years)=2013确保年份恰好覆盖2010到2013（因为是连续的四个年份）。

方法二：明确排除非法年份并验证数量

SELECT student_id, years
FROM my_data
WHERE student_id IN (
    SELECT student_id
    FROM my_data
    GROUP BY student_id
    HAVING SUM(CASE WHEN years NOT IN (2010,2011,2012,2013) THEN 1 ELSE 0 END) = 0
       AND COUNT(DISTINCT years) = 4
)

逻辑：SUM(CASE...) = 0确保没有年份超出目标范围，COUNT(DISTINCT years)=4确保学生拥有全部四个目标年份。

两种方法都能得到仅包含student_id=124的正确结果。

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