mysql --求出所有成绩大于每门平均值的学生uid

create table score(
uid VARCHAR(10),
subject_id VARCHAR(10),
score int)
DROP TABLE score
INSERT INTO score VALUES
('1001','01' ,88),
('1001','02' ,98),
('1001','03', 91),
('1002','01', 88),
('1002','02', 85),
('1002','03', 80),
('1003','01', 55),
('1003','02', 65),
('1003','03', 75);
--求出所有成绩大于每门平均值的学生uid
SELECT sc.uid
FROM
(
SELECT uid,min(score) mi
FROM score
GROUP BY uid
) sc,
(
SELECT max(tab.av) ma
FROM
(
SELECT AVG(score) av
FROM score
GROUP BY subject_id
) tab
)tab1
where sc.mi > tab1.ma