住房和城乡建设部网站,如何让自己做的博客网站上线,学销售从哪里开始,wordpress 移动端网页目录597. 好友申请 I#xff1a;总体通过率602. 好友申请 II #xff1a;谁有最多的好友603. 连续空余座位1045. 买下所有产品的客户597. 好友申请 I#xff1a;总体通过率
官方讲的题目太繁琐了#xff0c;大概就是#xff08;表2中列1列2不全相同的行数#xff09;/总体通过率602. 好友申请 II 谁有最多的好友603. 连续空余座位1045. 买下所有产品的客户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 1603. 连续空余座位
题型连续满足条件的行记录才输出即输出与否与前后行相关 解答错开一位的自连接 解法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_id1
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_id1045. 买下所有产品的客户
题型表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)
