如何编写SQL查询按name分组、按version统计行数并补全缺失版本计数？

这是个很常见的分组统计需求——既要按name和version统计行数，还要把某个name没出现过的version也列出来，并且计数补0。核心思路是先生成所有可能的name-version组合，再和实际的统计结果做左连接，把空值转成0就行。

假设你的表名为items（可以替换成你的实际表名），下面是具体的SQL实现： #

方式一：用CTE（适用于支持CTE的数据库，比如MySQL 8+、PostgreSQL、SQL Server等）

WITH all_possible_pairs AS (
    -- 生成所有name和version的笛卡尔积，得到全部可能的组合
    SELECT DISTINCT t1.name, t2.version
    FROM items t1
    CROSS JOIN (SELECT DISTINCT version FROM items) t2
),
actual_counts AS (
    -- 统计原表中每个name-version的实际行数
    SELECT name, version, COUNT(*) AS count
    FROM items
    GROUP BY name, version
)
-- 左连接组合表和统计结果，用COALESCE把NULL转为0
SELECT app.name, app.version, COALESCE(ac.count, 0) AS count
FROM all_possible_pairs app
LEFT JOIN actual_counts ac 
    ON app.name = ac.name 
    AND app.version = ac.version
ORDER BY app.name, app.version;

方式二：用子查询（适用于不支持CTE的老版本数据库）

SELECT app.name, app.version, IFNULL(ac.count, 0) AS count
FROM (
    -- 生成所有可能的name-version组合
    SELECT DISTINCT t1.name, t2.version
    FROM items t1
    CROSS JOIN (SELECT DISTINCT version FROM items) t2
) app
LEFT JOIN (
    -- 实际统计结果
    SELECT name, version, COUNT(*) AS count
    FROM items
    GROUP BY name, version
) ac 
    ON app.name = ac.name 
    AND app.version = ac.version
ORDER BY app.name, app.version;

代码解释： #

1. 生成所有组合：通过CROSS JOIN（交叉连接）把不重复的name列表和不重复的version列表做笛卡尔积，这样每个name都会对应所有存在的version（比如paper就会有(1)和(2)两个版本项）。
2. 实际统计：常规的分组统计，得到每个name-version的实际行数。
3. 左连接补0：左连接之后，那些没有实际统计结果的组合会返回NULL，用COALESCE或IFNULL把NULL转换成0，就得到了你想要的完整结果。

验证结果： #

执行后会输出和你期望完全一致的结果：

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