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

## 建表语句
create table if not exists score(uid int, subject_id int,score int)row format delimited fields terminated by '\t';
+------------+-------------------+--------------+--+
| score.uid  | score.subject_id  | score.score  |
+------------+-------------------+--------------+--+
| 1          | 1                 | 50           |
| 1          | 2                 | 60           |
| 1          | 3                 | 70           |
| 2          | 1                 | 70           |
| 2          | 2                 | 60           |
| 2          | 3                 | 80           |
| 3          | 1                 | 20           |
| 3          | 2                 | 60           |
| 3          | 3                 | 70           |
+------------+-------------------+--------------+--+
##找出所有科目成绩都大于某一学科平均成绩的用户
1. 先查出平均成绩
select subject_id, avg(score) as avgScore from score group by subject_id;
+-------------+---------------------+--+
| subject_id  |      avgscore       |
+-------------+---------------------+--+
| 1           | 46.666666666666664  |
| 2           | 60.0                |
| 3           | 73.33333333333333   |
+-------------+---------------------+--+
2. 拼接到一行
select t.uid, t.subject_id, t.score, t1.avgScore from score t left join (select subject_id, avg(score) as avgScore from score group by subject_id) t1 on t.subject_id = t1.subject_id;
+--------+---------------+----------+---------------------+--+
| t.uid  | t.subject_id  | t.score  |     t1.avgscore     |
+--------+---------------+----------+---------------------+--+
| 1      | 1             | 50       | 46.666666666666664  |
| 1      | 2             | 60       | 60.0                |
| 1      | 3             | 70       | 73.33333333333333   |
| 2      | 1             | 70       | 46.666666666666664  |
| 2      | 2             | 60       | 60.0                |
| 2      | 3             | 80       | 73.33333333333333   |
| 3      | 1             | 20       | 46.666666666666664  |
| 3      | 2             | 60       | 60.0                |
| 3      | 3             | 70       | 73.33333333333333   |
+--------+---------------+----------+---------------------+--+
3. 查询出偏科同学数据
select t.uid, t.subject_id, t.score, t1.avgScore from score t left join (select subject_id, avg(score) as avgScore from score group by subject_id) t1 on t.subject_id = t1.subject_id where t.score < t1.avgScore;
+--------+---------------+----------+---------------------+--+
| t.uid  | t.subject_id  | t.score  |     t1.avgscore     |
+--------+---------------+----------+---------------------+--+
| 1      | 3             | 70       | 73.33333333333333   |
| 3      | 1             | 20       | 46.666666666666664  |
| 3      | 3             | 70       | 73.33333333333333   |
+--------+---------------+----------+---------------------+--+
4. 过滤数据取相反逻辑
select t3.uid from score t3 left join (select t.uid, t.subject_id,t.score,t1.avgScore from score t left join (select subject_id, avg(score) as avgScore from score group by subject_id)t1 on t.subject_id = t1.subject_id where t.score < t1.avgScore)t2 on t2.uid = t3.uid where t2.uid is null;
+---------+--+
| t3.uid  |
+---------+--+
| 2       |
| 2       |
| 2       |
+---------+--+
5. 对数据去重获取最终结果
select uid from ( select t3.uid from score t3 left join ( select t.uid, t.subject_id, t.score, t1.avgScore from score t left join ( select subject_id, avg(score) as avgScore from score group by subject_id)t1 on t.subject_id = t1.subject_id where t.score < t1.avgScore)t2 on t2.uid = t3.uid where t2.uid is null) t4 group by uid;
+------+--+
| uid  |
+------+--+
| 2    |
+------+--+
##找出所有科目成绩都大于某一学科平均成绩的用户
1. 先查出平均成绩
select subject_id, avg(score) as avgScore from score group by subject_id;
2. 拼接到一行
select t.uid, t.subject_id, t.score, t1.avgScore from score t left join (select subject_id, avg(score) as avgScore from score group by subject_id) t1 on t.subject_id = t1.subject_id;
3. 取成绩小于平均成绩的同学数据
select t.uid, t.subject_id, t.score, t1.avgScore from score t left join (select subject_id, avg(score) as avgScore from score group by subject_id) t1 on t.subject_id = t1.subject_id where t.score < t1.avgScore;
4. 按相反逻辑取数据拿到大于平均成绩的同学
select t3.uid, t3.subject_id, t3.score from score t3 left join (select t.uid, t.subject_id, t.score, t1.avgScore from score t left join (select subject_id, avg(score) as avgScore from score group by subject_id) t1 on t.subject_id = t1.subject_id where t.score < t1.avgScore) t2 on t2.uid = t3.uid where t2.uid is null;
5. 对数据去重获取最终结果
select uid,subject_id,score from(select t3.uid, t3.subject_id, t3.score from score t3 left join (select t.uid, t.subject_id, t.score, t1.avgScore from score t left join (select subject_id, avg(score) as avgScore from score group by subject_id) t1 on t.subject_id = t1.subject_id where t.score < t1.avgScore) t2 on t2.uid = t3.uid where t2.uid is null) t4 group by uid,subject_id,score;
+------+-------------+--------+--+
| uid  | subject_id  | score  |
+------+-------------+--------+--+
| 2    | 1           | 70     |
| 2    | 2           | 60     |
| 2    | 3           | 80     |
+------+-------------+--------+--+