SQL GROUP BY分组时如何统计满足某列大于另一列的行数？

解决方案 #

你可以通过条件聚合的方式实现后两列的统计，核心逻辑是用CASE WHEN逐行判断符合条件的记录，再对结果求和得到对应行数。

修改后的完整查询语句如下：

SELECT 
 team_id,
 SUM(money_spent) as total_money_spent,
 SUM(money_budget) as total_money_budget,
 SUM(CASE WHEN money_spent > money_budget THEN 1 ELSE 0 END) AS days_over_spent,
 SUM(CASE WHEN money_spent < money_budget THEN 1 ELSE 0 END) AS days_under_spent
FROM money_table
GROUP BY team_id
ORDER BY team_id ASC

如果使用支持IF函数的SQL方言（比如MySQL），可以简化为如下写法：

SELECT 
 team_id,
 SUM(money_spent) as total_money_spent,
 SUM(money_budget) as total_money_budget,
 SUM(IF(money_spent > money_budget, 1, 0)) AS days_over_spent,
 SUM(IF(money_spent < money_budget, 1, 0)) AS days_under_spent
FROM money_table
GROUP BY team_id
ORDER BY team_id ASC

两种写法的运行结果完全匹配你要求的输出格式。

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