MySQL v5.5.33中基于两表的Join、Count与Rank实现求助

针对你在MySQL 5.5.33中的需求，我们可以通过子查询获取接收数，结合用户变量实现排名，最终得到你想要的统计结果。以下是完整的解决方案：

完整SQL代码 #

SELECT 
    final.id,
    final.name,
    final.total_sent,
    final.total_received,
    final.sent_minus_received,
    @rank := @rank + 1 AS rank
FROM (
    SELECT 
        a.id,
        a.name,
        COUNT(m.id_from) AS total_sent,
        (SELECT COUNT(*) FROM member_points WHERE id_to = a.id) AS total_received,
        COUNT(m.id_from) - (SELECT COUNT(*) FROM member_points WHERE id_to = a.id) AS sent_minus_received
    FROM members AS a
    LEFT JOIN member_points AS m ON a.id = m.id_from
    GROUP BY a.id, a.name
    ORDER BY sent_minus_received DESC
) AS final,
(SELECT @rank := 0) AS r;

代码细节解释 #

• total_sent：和你原来的逻辑一致，通过LEFT JOIN关联member_points表，统计当前成员作为id_from的记录数，也就是发送次数。
• total_received：使用子查询统计member_points表中当前成员作为id_to的记录数，得到接收次数。
• sent_minus_received：直接用total_sent减去total_received，得到发送与接收的差值。
• rank排名：由于MySQL 5.5不支持窗口函数（如ROW_NUMBER()），我们使用用户变量@rank来实现排名：
  1. 先定义初始值为0的变量@rank
  2. 在按sent_minus_received降序排序的结果集中，逐行递增@rank值，得到最终排名

执行结果 #

运行上述代码后，你会得到和预期完全一致的结果：

内容的提问来源于stack exchange，提问作者Andrew