力扣SQL刷题4

1158. 市场分析 I

题型：表1和表2连接时，如何把没有对应数据输出来。即表1中所有id列对应的表2数据输出，没用的输出0
解答1：left join 后用on筛选而不用where筛选
解答2：left join 后，select中用ifnull(x1, x2)
where和on的区别，on是在连接构造临时表时执行的，不管on中条件是否成立都会返回主表（也就是left join左边的表）的内容，where是在临时表形成后执行筛选作用的，不满足条件的整行都会被过滤掉。如果这里用的是 where year(order_date)=‘2019’ 那么得到的结果将会把不满足条件的user_id为3，4的行给删掉。用on的话会保留user_id为3，4的行。

ifnull(x1, x2) 函数：
ifnull(x1, x2) ：如果 x1 为 NULL就返回 x2，否则返回 x1。


法一：把筛选条件加在on里面

select u.user_id as buyer_id,u.join_date,count(order_id) orders_in_2019
from Orders o right join Users u on o.buyer_id = u.user_id and year(o.order_date) = 2019
group by u.user_id

法二：次数在一个表里先算出来，再右连接另一个表，次数为空的用一下ifnull函数

select u.user_id  as buyer_id,u.join_date,ifnull(s.cs,0) as orders_in_2019
from Users u
left join
(select buyer_id,count(order_date) cs
from Orders
where year(order_date) = 2019
group by buyer_id ) s
on u.user_id = s.buyer_id

1280. 学生们参加各科测试的次数

题型：表a×表b的所有可能，再结合表c的数量
解答：cross join的使用
from a cross join b left join c on a.列= c.列 and b.列= c.列

SELECT a.student_id, a.student_name, b.subject_name, COUNT(e.subject_name) AS attended_exams
FROM Students a CROSS JOIN Subjects b
    LEFT JOIN Examinations e ON a.student_id = e.student_id AND b.subject_name = e.subject_name
GROUP BY a.student_id, b.subject_name
ORDER BY a.student_id, b.subject_name

1174. 即时食物配送 II

题型：1，满足某种要求的占比 2， 删选出列A中不同种类的列B最早/小的记录
解题：1，打标签if(条件,1,0) 2，窗口函数排序 ，or 用min()

select round(sum(r1)/count(1)*100,2) as immediate_percentage
from 
(select *,if(order_date=customer_pref_delivery_date,1,0) as r1,
rank()over(partition by customer_id order by order_date ) as r2
 from Delivery) a
where r2=1

585. 2016年的投资

题型：要输出的记录满足： 列A值不唯一（与其他行有相同的），列B和列C的值唯一（B和C不能和其他记录完全相同）
思路：1，自连接，2，group by和having
解题1：把列A值不唯一的id值筛选出来，where id in (select distinct id from 啊a1 join a2 on a1.列A = a2.列A and a1.id = a2.id) 同理处理列B和列C

解法2：用group by 和count一起
where 列A in （select 列A from 表 group by 列A having count(*)>1）
列B和列C用concat连接起来

题目：TIV_2015列不唯一，LAT 和LON列一起不能和其他行重合。计算满足这样的条件中TIV_2016列求和

解法1中，left join 后on 条件1 and 条件2 会报错，而写成下面两种不会
原因在之前讲过，join 表1 和表2后，筛选条件放在on后面和where后面的区别

select round(sum(TIV_2016),2) as TIV_2016
from insurance
where
PID in
(select distinct a.PID
from insurance a join insurance b on a.TIV_2015 = b.TIV_2015 and a.PID !=b.PID) 
and
PID NOT in
(select distinct a.PID
from insurance a join insurance b on a.LAT = b.LAT and a.PID !=b.PID and a.LON = b.LON) 

select round(sum(TIV_2016),2) as TIV_2016
from insurance
where
PID in
(select distinct a.PID
from insurance a  left join insurance b on a.TIV_2015 = b.TIV_2015 where  a.PID !=b.PID) 
and
PID NOT in
(select distinct a.PID
from insurance a left join insurance b on a.LAT = b.LAT and a.PID !=b.PID where a.LON = b.LON) 

select round(sum(TIV_2016),2) as TIV_2016
from insurance
where
TIV_2015 in
(select TIV_2015
from insurance group by TIV_2015 having count(*)>1) 
and
concat(LAT, LON) in
(select concat(LAT, LON)
from insurance group by LAT, LON having count(*)=1)