Java中平均成绩大于70的判断,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

fromscore 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 |

+------+-------------+--------+--+