相关子查询/三表连接

1. 计算每天的注册用户，购买人数和当天购买人数

第一种 相关子查询

select u_date,count(distinct u_id) as '注册人数'
,(
    select count(distinct u2.u_id) as '购买人数'
    from user_table u2 join order_table o on u2.u_id=o.u_id
    where u1.u_date=u2.u_date #相关子查询
    group by u2.u_date
)
,(
      select count(distinct u3.u_id) as '当天购买人数'
      from user_table u3 join order_table o on u3.u_id=o.u_id and u3.u_date=o.o_date
      where u1.u_date=u3.u_date #相关子查询
      group by u3.u_date
)
from user_table u1
group by u1.u_date
order by u1.u_date;

第二种 三表连接

select t1.u_date,'注册人数','购买人数','当天购买人数'
from(
    select u_date,count(distinct u_id) as '注册人数'
    from user_table u1
    group by u_date
) as t1 
join (
     select u2.u_date,count(distinct u2.u_id) as '购买人数'
     from user_table u2 join order_table o on u2.u_id=o.u_id
     group by u2.u_date
) as t2 on t1.u_date=t2.u_date
join (
     select u3.u_date,count(distinct u3.u_id) as '当天购买人数'
     from user_table u3 join order_table o on u3.u_id=o.u_id and u3.u_date=o.o_date
     group by u3.u_date
) as t3 on t1.u_date=t3.u_date
order by t1.u_date;

2.牛客每次考试完，都会有一个成绩表(grade)，如下:

请你写一个sql语句查询用户分数大于其所在工作(job)分数的平均分的所有grade的属性，并且以id的升序排序

#用户分数大于其所在工作(job)分数的平均分的所有grade
select *
from grade g1
where score > (
    select avg(score)
    from grade g2
    where g1.job=g2.job #相关子查询
    group by job
)
order by id;