如何编写SQL查询获取近2年平均成绩均超54的学生？

表结构 #

创建的表结构如下：

create table years (
  yr bigint,
  average decimal(10,2),
  rollno bigint
)

测试数据 #

该表存储2020和2021年学生的平均成绩，数据如下：

year    average rollno
2021    55.20   1
2020    55.50   1
2020    54.50   2
2020    55.50   3
2021    55.40   3

需求与期望结果 #

需要筛选出近2年每年平均成绩均超过54分的学生，最终期望结果为：

year average rollno
2020  55.50   1
2021  55.20   1
2020  55.50   3
2021  55.40   3

错误查询分析 #

尝试过两个查询，均无法得到正确结果：

1. 无结果的查询：

select rollno 
from years
where average > 54
and yr = (YEAR(GETDATE())-1)
and yr = (YEAR(GETDATE())-2)

错误原因：yr不可能同时等于两个不同年份，条件冲突导致无返回结果。

2. 错误包含学号2的查询：

select rollno 
from years 
where average > 54
and yr = (YEAR(GETDATE())-1) or yr = (YEAR(GETDATE())-2)

错误原因：逻辑运算符优先级问题，and优先级高于or，导致条件实际为(average>54且yr=去年) 或者 yr=前年，学号2在2020年的成绩达标，但2021年无数据，却被错误包含。

正确SQL查询语句 #

方法1：分组筛选法 #

先筛选出两年内成绩达标且每年都有记录的学生，再关联原表获取详细数据：

SELECT y.*
FROM years y
JOIN (
    SELECT rollno
    FROM years
    WHERE yr IN (YEAR(GETDATE())-2, YEAR(GETDATE())-1)
      AND average > 54
    GROUP BY rollno
    HAVING COUNT(DISTINCT yr) = 2
) valid_students ON y.rollno = valid_students.rollno
WHERE yr IN (YEAR(GETDATE())-2, YEAR(GETDATE())-1)
ORDER BY y.rollno, y.yr;

方法2：窗口函数法 #

通过窗口函数统计每个学生在目标年份内达标记录的年份数：

SELECT yr, average, rollno
FROM (
    SELECT *,
           COUNT(DISTINCT CASE WHEN average >54 THEN yr END) OVER (PARTITION BY rollno) AS valid_years
    FROM years
    WHERE yr IN (YEAR(GETDATE())-2, YEAR(GETDATE())-1)
) t
WHERE valid_years = 2
ORDER BY rollno, yr;

方法3：固定年份筛选（适用于已知2020、2021场景） #

如果不需要动态获取年份，直接指定目标年份：

SELECT y.*
FROM years y
WHERE y.rollno IN (
    SELECT rollno
    FROM years
    WHERE (yr=2020 AND average>54) 
      AND rollno IN (SELECT rollno FROM years WHERE yr=2021 AND average>54)
)
AND yr IN (2020,2021)
ORDER BY rollno, yr;

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