HQL练习1

表结构: 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     |
+------+-------------+--------+--+

评论

暂无评论