表结构： uid,subject_id,score
求： 找出所有科目成绩都大于某一学科平均成绩的用户
建表语句
create table if not exists score(uid string,subject string,score int)
row format delimited
fields terminated by '/t';
t表+------+--+
| uid |
+------+--+
| 2 |
+------+--+
+------------+-------------------+--------------+--+
| 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.先查出平均成绩 t1表
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.拼接到一行 t2表
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.查询出偏科同学数据 t3表
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.过滤数据取相反逻辑 t4
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;
##找出所有科目成绩都大于某一学科平均成绩的用户
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 |
+------+-------------+--------+--+
