如何在MySQL中合并多表分组统计结果为统一宽表

我有5个对应不同数据集的表，对每张表执行了类似下面的分组查询来获取统计结果：

select number,count(*) as total from tb01 group by number;
select number,count(*) as total from tb02 group by number;

（注：原查询里的limit 5仅为示例，实际不需要限制行数）

其中一张表的查询结果示例如下：

+-----------+-------+
| number    | total |
+-----------+-------+
| 114000259 |     1 |
| 114000400 |     1 |
| 114000686 |     1 |
| 114000858 |     1 |
| 114003895 |     1 |
+-----------+-------+

现在需要把这5个分组统计结果合并成如下格式的整合表格（列名可自定义）：

+-----------+-------+-------+-------+
| number    | tb01  | tb02  | tb03  |
+-----------+-------+-------+-------+
| 114000259 |     1 |     2 |     1 |
| 114000400 |     1 |     0 |     1 |
| 114000686 |     1 |     3 |     1 |
| 114000858 |     1 |     1 |     5 |
| 114003895 |     1 |     0 |     1 |
+-----------+-------+-------+-------+

解决方案 #

方法1：SQL全连接+聚合（支持全连接的数据库：PostgreSQL、SQL Server等） #

通过FULL OUTER JOIN关联所有表的分组结果，用COALESCE将空值替换为0：

SELECT
    COALESCE(t1.number, t2.number, t3.number, t4.number, t5.number) AS number,
    COALESCE(t1.total, 0) AS tb01_count,
    COALESCE(t2.total, 0) AS tb02_count,
    COALESCE(t3.total, 0) AS tb03_count,
    COALESCE(t4.total, 0) AS tb04_count,
    COALESCE(t5.total, 0) AS tb05_count
FROM
    (SELECT number, COUNT(*) AS total FROM tb01 GROUP BY number) t1
FULL OUTER JOIN
    (SELECT number, COUNT(*) AS total FROM tb02 GROUP BY number) t2 ON t1.number = t2.number
FULL OUTER JOIN
    (SELECT number, COUNT(*) AS total FROM tb03 GROUP BY number) t3 ON COALESCE(t1.number, t2.number) = t3.number
FULL OUTER JOIN
    (SELECT number, COUNT(*) AS total FROM tb04 GROUP BY number) t4 ON COALESCE(t1.number, t2.number, t3.number) = t4.number
FULL OUTER JOIN
    (SELECT number, COUNT(*) AS total FROM tb05 GROUP BY number) t5 ON COALESCE(t1.number, t2.number, t3.number, t4.number) = t5.number
ORDER BY number;

方法2：UNION ALL+分组聚合（通用所有SQL数据库） #

先合并所有表的分组结果，再按number分组统计各表计数，适合不支持全连接的数据库（如MySQL）：

SELECT
    number,
    SUM(CASE WHEN source = 'tb01' THEN total ELSE 0 END) AS tb01_count,
    SUM(CASE WHEN source = 'tb02' THEN total ELSE 0 END) AS tb02_count,
    SUM(CASE WHEN source = 'tb03' THEN total ELSE 0 END) AS tb03_count,
    SUM(CASE WHEN source = 'tb04' THEN total ELSE 0 END) AS tb04_count,
    SUM(CASE WHEN source = 'tb05' THEN total ELSE 0 END) AS tb05_count
FROM
    (SELECT number, COUNT(*) AS total, 'tb01' AS source FROM tb01 GROUP BY number
     UNION ALL
     SELECT number, COUNT(*) AS total, 'tb02' AS source FROM tb02 GROUP BY number
     UNION ALL
     SELECT number, COUNT(*) AS total, 'tb03' AS source FROM tb03 GROUP BY number
     UNION ALL
     SELECT number, COUNT(*) AS total, 'tb04' AS source FROM tb04 GROUP BY number
     UNION ALL
     SELECT number, COUNT(*) AS total, 'tb05' AS source FROM tb05 GROUP BY number) AS combined
GROUP BY number
ORDER BY number;

方法3：Python Pandas处理（适合导出后离线处理） #

将各表统计结果导出为CSV后，用Pandas合并：

import pandas as pd

# 读取各表统计结果（假设已导出为CSV文件）
df1 = pd.read_csv('tb01_stats.csv')
df2 = pd.read_csv('tb02_stats.csv')
df3 = pd.read_csv('tb03_stats.csv')
df4 = pd.read_csv('tb04_stats.csv')
df5 = pd.read_csv('tb05_stats.csv')

# 全外连接合并，空值填充0
merged_df = df1.merge(df2, on='number', how='outer', suffixes=('_tb01', '_tb02')) \
              .merge(df3, on='number', how='outer') \
              .merge(df4, on='number', how='outer', suffixes=('', '_tb04')) \
              .merge(df5, on='number', how='outer', suffixes=('_tb03', '_tb05'))

# 填充空值并重命名列
merged_df = merged_df.fillna(0)
merged_df.rename(columns={
    'total_tb01': 'tb01',
    'total_tb02': 'tb02',
    'total_tb03': 'tb03',
    'total_tb04': 'tb04',
    'total_tb05': 'tb05'
}, inplace=True)

# 输出或保存结果
print(merged_df)
merged_df.to_csv('combined_stats.csv', index=False)

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