-
找出 总价大于100的人,但是只买了手机的人不统计。
1) 思路一:总价大于100的人和不只买了手机的人关联
select t1.name
from (
select name
from order
group by name
having sum(price)>100
) t1
join (
select name
from order
group by name
having sum(if(product='手机',0,1))!=0
) t2
on t1.name=t2.name;
- 用一条SQL,查出每门课都大于80分的学生姓名。
1) 思路一:找出大于80分的课程,按照姓名分组,选择课程数=总课程数的分组的姓名。
select
name
from (
select
name
from score
where score>80
) t
group by NAME
having count(*)=3;
- 求:找出所有科目成绩都大于某一学科平均成绩的学生
数据集如下:
uid,subject_id,score
1001 01 90
1001 02 90
1001 03 90
1002 01 85
1002 02 85
1002 03 70
1003 01 70
1003 02 70
1003 03 85
1)建表语句
create table score(
uid string,
subject_id string,
score int)
row format delimited fields terminated by '\t';
思路一:
①求出每个学科平均成绩
select
uid,
score,
avg(score) over(partition by subject_id) avg_score
from
score;t1
②根据是否大于平均成绩记录flag,大于则记为0否则记为1
select
uid,
if(score>avg_score,0,1) flag
from
t1;t2
③根据学生id进行分组统计flag的和,和为0则是所有学科都大于平均成绩
select
uid
from
t2
group by
uid
having
sum(flag)=0;
④最终SQL
select
uid
from
(select
uid,
if(score>avg_score,0,1) flag
from
(select
uid,
score,
avg(score) over(partition by subject_id) avg_score
from
score)t1)t2
group by
uid
having
sum(flag)=0;
思路二:
①在每一行成绩上求出该科目的平均成绩
select
uid,
subject_id,
score,
avg(score) over(partition by subject_id) avg_score
from socre;=>t1
②where选出成绩大于平均成绩的行,然后按照uid分组判断是否是所有科目都存在。
select
uid
from t1
where score>avg_score
group by uid
having count(*)=3