SQL每日一题(20220629)
参考:https://mp.weixin.qq.com/s/Aua_6hG0-7u3asp-7w3hwA
220629
题目
在 微信 或者 QQ这样的社交应用中,人们经常会发好友申请也会收到其他人的好友申请。
表 F0629 存储了所有好友申请通过的数据记录,其中,requester_id 和 accepter_id 都是用户的编号。
requester_id | accepter_id | accept_date |
---|---|---|
1 | 2 | 2016-06-03 |
1 | 3 | 2016-06-08 |
2 | 3 | 2016-06-08 |
3 | 4 | 2016-06-09 |
写一个查询语句,求出谁拥有最多的好友和他拥有的好友数目。对于上面的样例数据,结果为:
id | num |
---|---|
3 | 3 |
数据脚本
CREATE TABLE F0629
(
requester_id INT,
accepter_id INT,
accept_date DATE
);
INSERT INTO F0629 VALUES (1, 2, '2016-6-3');
INSERT INTO F0629 VALUES (1, 3, '2016-6-8');
INSERT INTO F0629 VALUES (2, 3, '2016-6-8');
INSERT INTO F0629 VALUES (3, 4, '2016-6-9');
我的答案
select a.id, count(a.id) as num
from (select requester_id as id
from f0629
union all
select accepter_id as id
from f0629) a
group by a.id
order by count(a.id) desc
limit 1;
参考答案
select a.IDS as ID, count(*) as NUM
from (select requester_id as IDS
from f0629
union all
select accepter_id as IDS
from f0629) a
group by a.IDS
order by count(*) desc
limit 1;
DJOSIMON