MySQL如何为统计代理房源状态的查询新增行列计算各行各列总和

实现方案 #

你可以用MySQL内置的WITH ROLLUP语法实现列汇总，同时直接新增聚合列计算每行的总和，完整SQL如下：

SELECT 
    SUM(t.status = 'D') AS draft,
    SUM(t.status = 'N') AS unpublish,
    SUM(t.status = 'Y') AS publish,
    SUM(t.status = 'S') AS sold,
    SUM(t.status = 'T') AS let,
    COUNT(*) AS agent_total, -- 行总和：当前代理人的所有房源总数
    IF(GROUPING(c.name), '总计', c.name) AS name
FROM listings t 
INNER JOIN agents c ON t.agent_id = c.id
GROUP BY t.agent_id, c.name WITH ROLLUP
HAVING GROUPING(t.agent_id) = 0 OR GROUPING(c.name) = 1;

逻辑说明

• 新增的agent_total列就是每行的总和，统计每个代理人名下所有状态的房源总数量
• WITH ROLLUP会在普通分组结果的最后自动追加一行总计，自动计算draft、unpublish等所有统计列的全局总和，也就是每列的汇总值
• GROUPING()函数用来区分普通行和ROLLUP生成的汇总行，汇总行的代理人名称会统一显示为「总计」
• HAVING子句用来过滤ROLLUP生成的多余中间汇总行，仅保留每个代理人的统计行和最终的总计行

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