HQL练习1

表结构： uid,subject_id,score
求：找出所有科目成绩都大于某一学科平均成绩的用户

建表语句

1create table if not exists score(uid string,subject string,score int)
2row format delimited
3fields terminated by '/t';

t表+------+--+
| uid |
+------+--+
| 2 |
+------+--+

1+------------+-------------------+--------------+--+
2| score.uid  | score.subject_id  | score.score  |
3+------------+-------------------+--------------+--+
4| 1          | 1                 | 50           |
5| 1          | 2                 | 60           |
6| 1          | 3                 | 70           |
7| 2          | 1                 | 70           |
8| 2          | 2                 | 60           |
9| 2          | 3                 | 80           |
10| 3          | 1                 | 20           |
11| 3          | 2                 | 60           |
12| 3          | 3                 | 70           |
13+------------+-------------------+--------------+--+

1.先查出平均成绩 t1表

1select subject_id,avg(score)as avgScore
2from score
3group by subject_id;
4+-------------+---------------------+--+
5| subject_id  |      avgscore       |
6+-------------+---------------------+--+
7| 1           | 46.666666666666664  |
8| 2           | 60.0                |
9| 3           | 73.33333333333333   |
10+-------------+---------------------+--+

2.拼接到一行 t2表

1select t.uid,t.subject_id,t.score,t1.avgScore from score t 
2left join (select subject_id,avg(score)as avgScore from score group by subject_id) t1 
3on t.subject_id=t1.subject_id
4;
5+--------+---------------+----------+---------------------+--+
6| t.uid  | t.subject_id  | t.score  |     t1.avgscore     |
7+--------+---------------+----------+---------------------+--+
8| 1      | 1             | 50       | 46.666666666666664  |
9| 1      | 2             | 60       | 60.0                |
10| 1      | 3             | 70       | 73.33333333333333   |
11| 2      | 1             | 70       | 46.666666666666664  |
12| 2      | 2             | 60       | 60.0                |
13| 2      | 3             | 80       | 73.33333333333333   |
14| 3      | 1             | 20       | 46.666666666666664  |
15| 3      | 2             | 60       | 60.0                |
16| 3      | 3             | 70       | 73.33333333333333   |
17+--------+---------------+----------+---------------------+--+

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;

1+--------+---------------+----------+---------------------+--+
2| t.uid  | t.subject_id  | t.score  |     t1.avgscore     |
3+--------+---------------+----------+---------------------+--+
4| 1      | 3             | 70       | 73.33333333333333   |
5| 3      | 1             | 20       | 46.666666666666664  |
6| 3      | 3             | 70       | 73.33333333333333   |
7+--------+---------------+----------+---------------------+--+

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;

1+---------+--+
2| t3.uid  |
3+---------+--+
4| 2       |
5| 2       |
6| 2       |
7+---------+--+

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##找出所有科目成绩都大于某一学科平均成绩的用户
21. 先查出平均成绩
3select subject_id, avg(score) as avgScore from score group by subject_id;
42. 拼接到一行
5select 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;
63. 取成绩小于平均成绩的同学数据
7select 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;
84. 按相反逻辑取数据拿到大于平均成绩的同学
9select 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;
105. 对数据去重获取最终结果
11select 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;
12+------+-------------+--------+--+
13| uid  | subject_id  | score  |
14+------+-------------+--------+--+
15| 2    | 1           | 70     |
16| 2    | 2           | 60     |
17| 2    | 3           | 80     |
18+------+-------------+--------+--+

评论区

相关推荐