力扣SQL刷题5

597. 好友申请 I：总体通过率

官方讲的题目太繁琐了，大概就是（表2中列1列2不全相同的行数）/（表1中列1列2不全相同的行数）
题型：如何统计表中列A与列B不全相同的行数
解题：select count(distinct 列A，列B) from 表名
繁琐一点的：select count(*) from (select distinct 列A，列B from 表)

select round(
    ifnull(
    (select count(distinct requester_id ,accepter_id) from RequestAccepted) / 
    (select count(distinct sender_id ,send_to_id) from FriendRequest)
    ,0)
    ,2) as accept_rate ;

select语句中可以没有from

602. 好友申请 II ：谁有最多的好友

题型：列A和列B的值一起考虑，分组计数输出次数最多的
解题：(select 列A from 表) union all (select 列B from 表)

select id,count(*) num
from 
(select requester_id id from RequestAccepted
union all
select accepter_id as id from RequestAccepted) a 
group by id
order by count(1) desc
limit 1

603. 连续空余座位

题型：连续满足条件的行记录才输出，即输出与否与前后行相关
解答：错开一位的自连接

解法1：

先和后一号的自连接，该行和下一行都满足条件，则输出该行
union
和前一号的自连接，该行和上一行都满足条件，则输出该行

select *
from
(
(select c1.seat_id seat_id
from Cinema c1 left join Cinema c2 on c1.seat_id = c2.seat_id-1
where c1.free = 1 and c2.free = 1)
union 
(select c1.seat_id seat_id
from Cinema c1 left join Cinema c2 on c1.seat_id = c2.seat_id+1
where c1.free = 1 and c2.free = 1)
) a
order by seat_id

解法2：解法1太繁琐,用abs(c1.seat_id - c2.seat_id) = 1把解法1中两种情况结合起来

select distinct c1.seat_id seat_id
from Cinema c1 left join Cinema c2 on abs(c1.seat_id - c2.seat_id) = 1
where c1.free = 1 and c2.free = 1
order by seat_id

1045. 买下所有产品的客户

题型：表1中，对列A分组，如果组中列B的值涵盖了所有表2的值，则输出对应的列A类别–见题清楚些
解法：利用外键的限制，不会有超出表2的情况，所以只要对比数量满足，就一定全部涵盖了

group by分组后，看各类别的计数和表2的是否相等

select customer_id
from Customer
group by customer_id
having count(distinct product_key) = (select count(product_key) from Product)