假设存在一个学生成绩表,包含3个字段。
id–学生编码
course–课程
score–分数
需要查询返回没门课程成绩都高于课程平均分的学生及课程成绩。
SELECT
*
FROM
(
SELECT
*,
COUNT(s.course) over(partition BY s.id) aaa
FROM
(
SELECT
t.id,
t.course,
t.score,
AVG(score) over(partition BY course) score_avg,--求每门课程平均分
COUNT(course) over(partition BY id) lines--求每个学生的课程数量
FROM
(
SELECT '001' id, '数学' course, 95 score
UNION ALL
SELECT '001' id, '语文' course, 95 score
UNION ALL
SELECT '001' id, '英语' course, 95 score
UNION ALL
SELECT '002' id, '数学' course, 80 score
UNION ALL
SELECT '002' id, '语文' course, 80 score
UNION ALL
SELECT '002' id, '英语' course, 80 score
UNION ALL
SELECT '003' id, '数学' course, 95 score
UNION ALL
SELECT '003' id, '语文' course, 70 score
UNION ALL
SELECT '003' id, '英语' course, 100 score
)
t
)
s
WHERE
s.score > s.score_avg
)
p
WHERE
p.aaa = p.lines```