mysql - MySQL Total Friends 找到多个最大值（如果存在）

Question

我有一个 MySQL 表，你有朋友列表。

+--------------+-------------+-------------+
| 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  |
|            1 |           5 | 2016-06-09  |
+--------------+-------------+-------------+

如果有多个人的朋友数量最多（在这种情况下，1 和 3 各有 3 个朋友），我如何打印 1 和 3 以及他们的计数。

我有部分可以找到第 N 个最大的人。

select s.requester_id, sum(s.total) as total_friends from (
select requester_id, count(accepter_id) as total from request_accepted
group by requester_id
union 
select accepter_id, count(requester_id) as total from request_accepted
group by accepter_id
  ) as s 
group by s.requester_id
order by total_friends desc;

标签： mysql

Answer 1

我设法想出了这个丑陋但有效的代码。

select s.requester_id, sum(s.total) as total_friends
from 
(select requester_id, count(accepter_id) as total 
from request_accepted
group by requester_id
union
select accepter_id, count(requester_id) as total
from request_accepted
group by accepter_id) as s
group by s.requester_id
having total_friends = (select max(s2.max_total)
from (select sum(s1.total) as max_total
      from (select requester_id, count(accepter_id) as total 
from request_accepted
group by requester_id
union
select accepter_id, count(requester_id) as total
from request_accepted
group by accepter_id) as s1
group by s1.requester_id) as s2)