SQL养成手册

1. 找出 总价大于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;

2. 用一条SQL，查出每门课都大于80分的学生姓名。
   

1） 思路一：找出大于80分的课程，按照姓名分组，选择课程数=总课程数的分组的姓名。

select
	name
from (
	select
		name
	from score
	where score>80
) t
group by NAME
having count(*)=3;

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