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