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;