SQLite3联合查询中按消息类型统计记录结果异常求助

你的问题出在GROUP BY子句的位置错误，导致UNION的逻辑完全偏离了预期。

错误原因拆解 #

你当前的查询语句中，GROUP BY message_type只作用于第二个SELECT（svd表的查询），而第一个SELECT（messages表的查询）并没有做分组统计——它会返回messages表中所有匹配的原始行（未聚合）。当这两个结果集执行UNION后再做GROUP BY时，会把messages表中同类型的所有行直接累加，最终得到的是该表的总记录数而非单类型的统计数，同时还会丢失那些只在messages表中存在的message_type。

修正后的查询方案 #

你需要先分别对两张表完成分组统计，再将两个统计结果集合并（根据需求选择合并求和或保留分表统计）：

方案1：合并同类型的跨表总记录数

如果需要统计每个message_type在两张表中的总条数，用UNION ALL合并子统计结果后再求和：

SELECT 
    combined.ID,
    combined.Type,
    SUM(combined.MsgCount) AS TotalMsgCount
FROM (
    -- 先统计messages表各类型数量
    SELECT 
        m.message_type AS ID,
        mt.type AS Type,
        COUNT(*) AS MsgCount
    FROM messages m
    INNER JOIN message_types mt ON m.message_type = mt.ID
    GROUP BY m.message_type, mt.type
    UNION ALL
    -- 再统计svd表各类型数量
    SELECT 
        s.message_type AS ID,
        mt.type AS Type,
        COUNT(*) AS MsgCount
    FROM svd s
    INNER JOIN message_types mt ON s.message_type = mt.ID
    GROUP BY s.message_type, mt.type
) AS combined
GROUP BY combined.ID, combined.Type
ORDER BY TotalMsgCount DESC;

方案2：保留两张表的单独统计结果（带来源标识）

如果想区分每条统计结果来自哪张表，可以新增一个来源字段：

SELECT 
    m.message_type AS ID,
    mt.type AS Type,
    COUNT(*) AS MsgCount,
    'messages' AS TableSource
FROM messages m
INNER JOIN message_types mt ON m.message_type = mt.ID
GROUP BY m.message_type, mt.type
UNION
SELECT 
    s.message_type AS ID,
    mt.type AS Type,
    COUNT(*) AS MsgCount,
    'svd' AS TableSource
FROM svd s
INNER JOIN message_types mt ON s.message_type = mt.ID
GROUP BY s.message_type, mt.type
ORDER BY MsgCount DESC;

原查询错误结果的深层逻辑 #

原查询的执行顺序完全不符合你的预期：

1. 从messages表关联message_types，返回所有匹配的原始行（共96513+46265+11098+961+452=156063行）
2. 从svd表关联message_types，分组统计后返回2行（类型5和24）
3. 将两个结果集UNION（自动去重，但此处无重复行）
4. 对合并后的全量结果执行GROUP BY message_type，此时类型1的行有156063条，所以count(*)直接变成了该表总记录数，其他只在messages表存在的类型因为是分散行，被UNION后无法正确分组，最终导致结果缺失。

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