力扣SQL刷题5
| 阿里云国内75折 回扣 微信号：monov8 |
| 阿里云国际，腾讯云国际，低至75折。AWS 93折 免费开户实名账号 代冲值 优惠多多 微信号：monov8 飞机：@monov6 |
目录
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)