求助：多表统计各活跃邮箱类型数量的SQL查询方案

核心SQL写法 #

通过对每个表单独统计后，使用UNION ALL合并结果集，直接得到符合要求的输出格式：

SELECT 
    'T1' AS TABLE_NAME,
    COUNT(CASE WHEN MY_ISP = 'gmail' THEN 1 END) AS GMAIL,
    COUNT(CASE WHEN MY_ISP = 'msft' THEN 1 END) AS MSFT,
    COUNT(CASE WHEN MY_ISP NOT IN ('gmail', 'msft', 'vmg') THEN 1 END) AS OTHER,
    COUNT(CASE WHEN MY_ISP = 'vmg' THEN 1 END) AS VMG
FROM Table1
WHERE STATUS = 'active'

UNION ALL

SELECT 
    'T2' AS TABLE_NAME,
    COUNT(CASE WHEN MY_ISP = 'gmail' THEN 1 END) AS GMAIL,
    COUNT(CASE WHEN MY_ISP = 'msft' THEN 1 END) AS MSFT,
    COUNT(CASE WHEN MY_ISP NOT IN ('gmail', 'msft', 'vmg') THEN 1 END) AS OTHER,
    COUNT(CASE WHEN MY_ISP = 'vmg' THEN 1 END) AS VMG
FROM Table2
WHERE STATUS = 'active'

-- 按上述格式依次添加Table3到Table15的统计语句

优化方案（通过视图简化） #

针对15个表的场景，可先创建统一视图整合数据，再分组统计，提升可维护性：

1. 创建整合所有表数据的视图：

CREATE VIEW AllEmailData AS
SELECT 'T1' AS TABLE_NAME, EMAIL, MY_ISP, STATUS FROM Table1
UNION ALL
SELECT 'T2' AS TABLE_NAME, EMAIL, MY_ISP, STATUS FROM Table2
UNION ALL
-- 继续添加Table3到Table15的SELECT语句
SELECT 'T15' AS TABLE_NAME, EMAIL, MY_ISP, STATUS FROM Table15;

2. 基于视图完成统计：

SELECT 
    TABLE_NAME,
    COUNT(CASE WHEN MY_ISP = 'gmail' THEN 1 END) AS GMAIL,
    COUNT(CASE WHEN MY_ISP = 'msft' THEN 1 END) AS MSFT,
    COUNT(CASE WHEN MY_ISP NOT IN ('gmail', 'msft', 'vmg') THEN 1 END) AS OTHER,
    COUNT(CASE WHEN MY_ISP = 'vmg' THEN 1 END) AS VMG
FROM AllEmailData
WHERE STATUS = 'active'
GROUP BY TABLE_NAME
ORDER BY TABLE_NAME;

关键说明 #

• CASE WHEN按MY_ISP分类计数，未匹配指定类型的邮箱归入OTHER
• WHERE STATUS = 'active'过滤出仅活跃状态的邮箱数据
• UNION ALL保留各表的独立统计结果，避免数据合并丢失表维度信息
• 视图方案可减少重复代码，后续表结构或数量变更时仅需修改视图定义

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