找出所有科目成绩都大于某一学科平均成绩的学生

 

 1.先求出每个学科的平均成绩

 SELECT user_id, subject_id, score, avg( score ) over ( PARTITION BY subject_id ) AS avg_score FROM student

 

 2.每个学科的成绩与平均学科成绩比较，大于记为1，小于为0

SELECT
user_id,
IF
( avg_score < score, 1, 0 ) AS flag
FROM
( SELECT user_id, subject_id, score, avg( score ) over ( PARTITION BY subject_id ) AS avg_score FROM student ) AS a

 

 

 

3.id分组，统计1的值

SELECT
user_id
FROM
(
SELECT
user_id,
IF
( avg_score < score, 1, 0 ) AS flag
FROM
( SELECT user_id, subject_id, score, avg( score ) over ( PARTITION BY subject_id ) AS avg_score FROM student ) AS a
) AS b
GROUP BY
user_id
HAVING
sum( flag ) = 3;