chengji
sid | cid | quarter | year | grade |
---|---|---|---|---|
A1 | math101 | Winter | 2013 | 4 |
A1 | Chinese102 | Spring | 2015 | 2 |
A1 | Chinese103 | Spring | 2015 | 3 |
A2 | math103 | Spring | 2017 | 4 |
A2 | math100 | Winter | 2019 | 0 |
A3 | Chinese100 | Winter | 2018 | 4 |
A3 | math102A | Fall | 2020 | 4 |
A3 | art101 | Winter | 2020 | 3 |
WITH t AS (
SELECT sid, year, quarter, AVG(grade) AS gpa,
ROW_NUMBER() OVER (PARTITION BY sid
ORDER BY year DESC, CASE quarter
WHEN 'Fall' THEN 3
WHEN 'Spring' THEN 2
WHEN 'Winter' THEN 1 END DESC) rn
FROM chengji
GROUP BY sid, year, quarter
)
SELECT sid, year, quarter, gpa
FROM t
WHERE rn = 1;
目前, 这个 sql 语句返回, 是想要的结果了
sid | year | quarter | gpa |
---|---|---|---|
A1 | 2015 | Spring | 2.5 |
A2 | 2019 | Winter | 0.0 |
A3 | 2020 | Winter | 3.0 |
有可 可以不用 ROW_NUMBER 和 CASE 语句 但是同样返回 一样的结果吗? sqlite 环境