SQL多表统计查询：如何统计多表中特定邮编前缀的记录数？

一、多表统计单个前缀（以BA为例） #

若要在包括原表在内的5个结构相似的表中统计p2字段前缀为BA的记录数，同时区分各表结果，可通过UNION ALL拼接各表查询语句：

SELECT 'dsf_2022_raw_new' AS table_name, 'BA' AS prefix, COUNT(*) AS record_count
FROM dsf_2022_raw_new
WHERE p2 LIKE 'BA%'
UNION ALL
SELECT 'dsf_2022_raw_1' AS table_name, 'BA' AS prefix, COUNT(*) AS record_count
FROM dsf_2022_raw_1
WHERE p2 LIKE 'BA%'
UNION ALL
SELECT 'dsf_2022_raw_2' AS table_name, 'BA' AS prefix, COUNT(*) AS record_count
FROM dsf_2022_raw_2
WHERE p2 LIKE 'BA%'
UNION ALL
SELECT 'dsf_2022_raw_3' AS table_name, 'BA' AS prefix, COUNT(*) AS record_count
FROM dsf_2022_raw_3
WHERE p2 LIKE 'BA%'
UNION ALL
SELECT 'dsf_2022_raw_4' AS table_name, 'BA' AS prefix, COUNT(*) AS record_count
FROM dsf_2022_raw_4
WHERE p2 LIKE 'BA%';

注：将dsf_2022_raw_1~dsf_2022_raw_4替换为实际的4个表名；若需精确匹配p2='BA'而非前缀匹配，去掉LIKE条件末尾的%即可。

二、多表统计多个前缀（BA、DL、TS、AL） #

若要一次性统计这四个前缀在所有5个表中的记录数，有两种实现方式：

方式1：按表分组，显示各表的前缀统计结果 #

每个表单独统计四个前缀的记录数，再合并结果：

-- 原表统计
SELECT 'dsf_2022_raw_new' AS table_name,
       SUM(CASE WHEN p2 LIKE 'BA%' THEN 1 ELSE 0 END) AS BA_count,
       SUM(CASE WHEN p2 LIKE 'DL%' THEN 1 ELSE 0 END) AS DL_count,
       SUM(CASE WHEN p2 LIKE 'TS%' THEN 1 ELSE 0 END) AS TS_count,
       SUM(CASE WHEN p2 LIKE 'AL%' THEN 1 ELSE 0 END) AS AL_count
FROM dsf_2022_raw_new
UNION ALL
-- 表1统计
SELECT 'dsf_2022_raw_1' AS table_name,
       SUM(CASE WHEN p2 LIKE 'BA%' THEN 1 ELSE 0 END) AS BA_count,
       SUM(CASE WHEN p2 LIKE 'DL%' THEN 1 ELSE 0 END) AS DL_count,
       SUM(CASE WHEN p2 LIKE 'TS%' THEN 1 ELSE 0 END) AS TS_count,
       SUM(CASE WHEN p2 LIKE 'AL%' THEN 1 ELSE 0 END) AS AL_count
FROM dsf_2022_raw_1
UNION ALL
-- 表2统计
SELECT 'dsf_2022_raw_2' AS table_name,
       SUM(CASE WHEN p2 LIKE 'BA%' THEN 1 ELSE 0 END) AS BA_count,
       SUM(CASE WHEN p2 LIKE 'DL%' THEN 1 ELSE 0 END) AS DL_count,
       SUM(CASE WHEN p2 LIKE 'TS%' THEN 1 ELSE 0 END) AS TS_count,
       SUM(CASE WHEN p2 LIKE 'AL%' THEN 1 ELSE 0 END) AS AL_count
FROM dsf_2022_raw_2
UNION ALL
-- 表3统计
SELECT 'dsf_2022_raw_3' AS table_name,
       SUM(CASE WHEN p2 LIKE 'BA%' THEN 1 ELSE 0 END) AS BA_count,
       SUM(CASE WHEN p2 LIKE 'DL%' THEN 1 ELSE 0 END) AS DL_count,
       SUM(CASE WHEN p2 LIKE 'TS%' THEN 1 ELSE 0 END) AS TS_count,
       SUM(CASE WHEN p2 LIKE 'AL%' THEN 1 ELSE 0 END) AS AL_count
FROM dsf_2022_raw_3
UNION ALL
-- 表4统计
SELECT 'dsf_2022_raw_4' AS table_name,
       SUM(CASE WHEN p2 LIKE 'BA%' THEN 1 ELSE 0 END) AS BA_count,
       SUM(CASE WHEN p2 LIKE 'DL%' THEN 1 ELSE 0 END) AS DL_count,
       SUM(CASE WHEN p2 LIKE 'TS%' THEN 1 ELSE 0 END) AS TS_count,
       SUM(CASE WHEN p2 LIKE 'AL%' THEN 1 ELSE 0 END) AS AL_count
FROM dsf_2022_raw_4;

该方式会以表为单位，每行显示一个表的四个前缀记录数。

方式2：按前缀分组，汇总所有表的总记录数 #

若无需区分单个表，仅需每个前缀在所有表中的总数，可使用如下语句：

SELECT prefix, SUM(record_count) AS total_count
FROM (
    -- BA前缀统计
    SELECT 'BA' AS prefix, COUNT(*) AS record_count FROM dsf_2022_raw_new WHERE p2 LIKE 'BA%'
    UNION ALL
    SELECT 'BA' AS prefix, COUNT(*) AS record_count FROM dsf_2022_raw_1 WHERE p2 LIKE 'BA%'
    UNION ALL
    SELECT 'BA' AS prefix, COUNT(*) AS record_count FROM dsf_2022_raw_2 WHERE p2 LIKE 'BA%'
    UNION ALL
    SELECT 'BA' AS prefix, COUNT(*) AS record_count FROM dsf_2022_raw_3 WHERE p2 LIKE 'BA%'
    UNION ALL
    SELECT 'BA' AS prefix, COUNT(*) AS record_count FROM dsf_2022_raw_4 WHERE p2 LIKE 'BA%'
    
    -- DL前缀统计
    UNION ALL
    SELECT 'DL' AS prefix, COUNT(*) AS record_count FROM dsf_2022_raw_new WHERE p2 LIKE 'DL%'
    UNION ALL
    SELECT 'DL' AS prefix, COUNT(*) AS record_count FROM dsf_2022_raw_1 WHERE p2 LIKE 'DL%'
    UNION ALL
    SELECT 'DL' AS prefix, COUNT(*) AS record_count FROM dsf_2022_raw_2 WHERE p2 LIKE 'DL%'
    UNION ALL
    SELECT 'DL' AS prefix, COUNT(*) AS record_count FROM dsf_2022_raw_3 WHERE p2 LIKE 'DL%'
    UNION ALL
    SELECT 'DL' AS prefix, COUNT(*) AS record_count FROM dsf_2022_raw_4 WHERE p2 LIKE 'DL%'
    
    -- TS前缀统计
    UNION ALL
    SELECT 'TS' AS prefix, COUNT(*) AS record_count FROM dsf_2022_raw_new WHERE p2 LIKE 'TS%'
    UNION ALL
    SELECT 'TS' AS prefix, COUNT(*) AS record_count FROM dsf_2022_raw_1 WHERE p2 LIKE 'TS%'
    UNION ALL
    SELECT 'TS' AS prefix, COUNT(*) AS record_count FROM dsf_2022_raw_2 WHERE p2 LIKE 'TS%'
    UNION ALL
    SELECT 'TS' AS prefix, COUNT(*) AS record_count FROM dsf_2022_raw_3 WHERE p2 LIKE 'TS%'
    UNION ALL
    SELECT 'TS' AS prefix, COUNT(*) AS record_count FROM dsf_2022_raw_4 WHERE p2 LIKE 'TS%'
    
    -- AL前缀统计
    UNION ALL
    SELECT 'AL' AS prefix, COUNT(*) AS record_count FROM dsf_2022_raw_new WHERE p2 LIKE 'AL%'
    UNION ALL
    SELECT 'AL' AS prefix, COUNT(*) AS record_count FROM dsf_2022_raw_1 WHERE p2 LIKE 'AL%'
    UNION ALL
    SELECT 'AL' AS prefix, COUNT(*) AS record_count FROM dsf_2022_raw_2 WHERE p2 LIKE 'AL%'
    UNION ALL
    SELECT 'AL' AS prefix, COUNT(*) AS record_count FROM dsf_2022_raw_3 WHERE p2 LIKE 'AL%'
    UNION ALL
    SELECT 'AL' AS prefix, COUNT(*) AS record_count FROM dsf_2022_raw_4 WHERE p2 LIKE 'AL%'
) AS temp
GROUP BY prefix;

该方式会输出每个前缀的总记录数，示例结果如下：

prefix  total_count
BA      xxx
DL      xxx
TS      xxx
AL      xxx

三、注意事项 #

• 确保所有表的p2字段类型一致，避免匹配异常；
• 若数据库对大小写敏感，可使用LOWER(p2) LIKE 'ba%'这类语句忽略大小写；
• 替换示例中的表名为实际使用的表名。

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