MySQL按date和type两列分组统计id总数并包含零计数项

要实现按date和type全组合分组统计id总数，且缺失组合显示0的需求，核心思路是先生成所有可能的(date, type)组合，再左连接原表进行统计，具体步骤和实现如下：

步骤拆解 #

• 第一步：获取所有不重复的date值和所有不重复的type值，通过笛卡尔积生成完整的组合列表，确保每个date都能对应到所有type
• 第二步：将这个完整组合列表与原表左连接，这样即使原表中某date下没有对应type的记录，也会保留该组合
• 第三步：对连接后的结果按date和type分组，统计id的数量，并用COALESCE或IFNULL把NULL值替换为0

具体SQL实现 #

假设你的表名为test（匹配你提供的示例表结构），对应的SQL语句如下：

-- 生成所有date和type的完整组合
WITH all_combinations AS (
    SELECT DISTINCT t1.`date`, t2.`type`
    FROM test t1
    CROSS JOIN test t2
)
SELECT 
    ac.`date`,
    ac.`type`,
    COALESCE(COUNT(t.id), 0) AS id_count
FROM all_combinations ac
LEFT JOIN test t 
    ON ac.`date` = t.`date` 
    AND ac.`type` = t.`type`
GROUP BY ac.`date`, ac.`type`
ORDER BY ac.`date`, ac.`type`;

代码细节解释 #

• WITH all_combinations AS (...)：用CTE（公共表表达式）生成所有date和type的笛卡尔积，确保没有遗漏任何(date, type)组合
• CROSS JOIN：生成两个集合的所有可能配对，让每个date都能和每个type组合一次
• LEFT JOIN：保留all_combinations中的所有记录，即使原表中没有对应匹配的行，保证缺失组合不会被过滤掉
• COALESCE(COUNT(t.id), 0)：当没有匹配记录时，COUNT(t.id)会返回NULL，用COALESCE把它转换成0，符合预期的显示要求
• GROUP BY ac.date, ac.type``：按完整的(date, type)组合分组统计，保证每个组合都有对应的统计结果

如果你的type值是固定的（比如已知只有'A'、'B'两类），也可以直接枚举type值来生成组合，这样效率会更高，示例如下：

WITH fixed_types AS (
    SELECT 'A' AS `type` UNION ALL
    SELECT 'B' AS `type`
),
all_combinations AS (
    SELECT DISTINCT t.`date`, ft.`type`
    FROM test t
    CROSS JOIN fixed_types ft
)
SELECT 
    ac.`date`,
    ac.`type`,
    COALESCE(COUNT(t.id), 0) AS id_count
FROM all_combinations ac
LEFT JOIN test t 
    ON ac.`date` = t.`date` 
    AND ac.`type` = t.`type`
GROUP BY ac.`date`, ac.`type`
ORDER BY ac.`date`, ac.`type`;

这种方式适合type值固定且数量不多的场景，避免了从原表取所有distinct type的开销。

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