MySQL自连接问题：如何正确统计用户近两月推荐数并合并查询

先来说说你遇到的两个问题的根源，再给出完整的解决方案：

1. 上月推荐数全员显示相同值的原因 #

你的PHP代码里，处理上月查询结果时的循环逻辑有问题：

while($values = mysqli_fetch_assoc($total)) {
    $previous = $values['previous_total'];
}

这个循环会把每一行的previous_total都覆盖赋值给$previous，循环结束后$previous只保留了最后一条记录的值，所以后续输出所有用户的上月数据时，自然全是同一个数。

不过其实我们可以通过合并SQL查询来彻底避免这个问题，同时让代码更高效。

2. 合并两个查询的最优方案：条件聚合 #

不需要用SELF JOIN或者INNER JOIN，直接用CASE WHEN结合聚合函数COUNT，就能在一个查询里同时统计当月和上月的推荐数，只需要一次数据库请求，性能更优。

修改后的SQL查询 #

SELECT 
    user_id,
    -- 统计当月推荐数：仅当日期属于当月时计数
    COUNT(CASE 
            WHEN MONTH(date) = MONTH(CURDATE()) AND YEAR(date) = YEAR(CURDATE()) 
            THEN refer_id 
          END) AS current_total,
    -- 统计上月推荐数：仅当日期属于上月时计数
    COUNT(CASE 
            WHEN MONTH(date) = MONTH(CURDATE() - INTERVAL 1 MONTH) 
            AND YEAR(date) = YEAR(CURDATE() - INTERVAL 1 MONTH) 
            THEN refer_id 
          END) AS previous_total
FROM your_table_name -- 替换成你的实际表名
WHERE date >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH) -- 只筛选最近两个月的数据，优化查询速度
GROUP BY user_id;

这里的关键点：

• COUNT会忽略NULL值，当CASE WHEN的条件不满足时返回NULL，不会被计入统计，刚好实现分月计数。
• 必须加上YEAR(date)的判断，避免跨年场景出错（比如1月的上月是去年12月，只判断月份会把今年12月的数据误算进去）。
• 加上WHERE条件筛选最近两个月的数据，避免扫描全表，提升查询效率。

修改后的完整PHP代码 #

<table class="table table-hover">
<thead>
<tr>
<th>Sales Name</th>
<th>Current Month</th>
<th>Previous Month</th>
</tr>
</thead>
<?php
// 合并后的查询语句
$query = "SELECT 
            user_id,
            COUNT(CASE WHEN MONTH(date) = MONTH(CURDATE()) AND YEAR(date) = YEAR(CURDATE()) THEN refer_id END) AS current_total,
            COUNT(CASE WHEN MONTH(date) = MONTH(CURDATE() - INTERVAL 1 MONTH) AND YEAR(date) = YEAR(CURDATE() - INTERVAL 1 MONTH) THEN refer_id END) AS previous_total
          FROM your_table_name
          WHERE date >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)
          GROUP BY user_id";

$result = mysqli_query($connection, $query);

// 处理活跃判断：先获取行数，再决定输出内容
$totalUsers = mysqli_num_rows($result);
if ($totalUsers < 5) {
    echo "<tr><td colspan='3'>Inactive!</td></tr>";
} else {
    while($values = mysqli_fetch_assoc($result)) {
        $user_id = $values['user_id'];
        // 处理没有推荐的情况，显示0而不是空值
        $currentRefer = $values['current_total'] ?? 0;
        $previousRefer = $values['previous_total'] ?? 0;
        
        echo "<tr><td>$user_id</td><td>$currentRefer</td><td>$previousRefer</td></tr>";
    }
}
?>
</table>
<!--END TABLE-->

这段代码做了这些优化：

• 合并了两个SQL查询，减少数据库交互次数。
• 修复了mysqli_num_rows的调用错误（原代码缺少参数$result）。
• 用?? 0处理用户当月/上月没有推荐的情况，避免显示空值。
• 把活跃判断放在循环前，避免先输出用户行再提示"Inactive!"的混乱情况。

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