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

建表

DROP TABLE IF EXISTS request_accepted; CREATE TABLE request_accepted ( requester_id INT, accepter_id INT, accept_date DATE ); INSERT INTO request_accepted VALUES(1, 2, '2016_06-03'); INSERT INTO request_accepted VALUES(1, 3, '2016-06-08'); INSERT INTO request_accepted VALUES(2, 3, '2016-06-08'); INSERT INTO request_accepted VALUES(3, 4, '2016-06-09');

解题思路

比如此测试表中：1号用户好友有2个、2号用户好友有2个、3号用户好友有3个、4号用户好友有1个 对requester_id和accepter_id分别进行分组查询统计个数，再联合查询，再分组降序排序 分页取第一个 如果有多个人有相同最多好友数，可以用rank 开窗

SELECT id, SUM(num) AS num FROM ( SELECT requester_id AS id, COUNT(1) AS num FROM request_accepted GROUP BY requester_id UNION ALL SELECT accepter_id AS id, COUNT(1) AS num FROM request_accepted GROUP BY accepter_id ) AS tmp GROUP BY id ORDER BY num DESC LIMIT 1