我有一个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;
我设法想出了这个丑陋但有效的代码。
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)