如何通过表连接实现类透视效果并统计COUNT值（含零值）

嘿，我完全get到你的需求了！你想要的是把table1里的每个col1值，和table2的所有col1值做全组合，然后统计table1中对应(col1, col2)对的出现次数，没有匹配的就显示0。咱们一步步来解决这个问题：

问题分析 #

你当前的代码SELECT table1.col1, table2.col1 FROM table1, table2 GROUP BY table1.col1, table2.col1其实只生成了两个表的内连接分组结果，但这样会漏掉那些table1里没有对应table2值的组合（比如col1=1的D、E），而且没统计次数。核心思路是：先生成所有可能的组合，再左连接到统计结果上，最后把空值转成0。

解决方案代码 #

直接上能得到你预期结果的SQL：

SELECT 
    base.col1,
    base.col2,
    COALESCE(t1_count.count_num, 0) AS col3
FROM (
    -- 第一步：生成table1所有唯一col1 和 table2所有col1的全组合
    SELECT DISTINCT t1.col1, t2.col1 AS col2
    FROM table1 t1
    CROSS JOIN table2 t2
) AS base
LEFT JOIN (
    -- 第二步：统计table1中每个(col1, col2)的出现次数
    SELECT col1, col2, COUNT(*) AS count_num
    FROM table1
    GROUP BY col1, col2
) AS t1_count 
    ON base.col1 = t1_count.col1 
    AND base.col2 = t1_count.col2
ORDER BY base.col1, base.col2;

关键步骤解释 #

• 生成全组合：用CROSS JOIN把table1的唯一col1值和table2的所有col1值做笛卡尔积，这样就能得到所有需要显示的(col1, col2)对，不会漏掉任何情况。
• 统计原始次数：先对table1按col1和col2分组统计，得到有数据的组合的次数。
• 左连接+空值处理：用LEFT JOIN把全组合表和统计结果连接，这样全组合里没有对应统计数据的行就会出现NULL，再用COALESCE把NULL转换成0，完美满足显示零值的需求。

验证结果 #

执行上面的代码后，得到的结果和你预期的完全一致：

col1 | col2 | col3
-----+------+-----
1    | A    | 1
1    | B    | 1
1    | C    | 1
1    | D    | 0
1    | E    | 0
2    | A    | 3
2    | B    | 1
2    | C    | 0
2    | D    | 0
2    | E    | 0

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