MySQL单查询实现两个表按entity_id聚合的行数差异TopN统计

当然可以用一条SQL语句直接搞定这个需求，不用手动导出数据再对比！结合UNION ALL和聚合函数，就能自动计算每个entity_id在两张表中的行数差异，还能利用你已经建好的entity_id索引保证查询效率。

核心查询语句 #

SELECT 
    entity_id,
    (SUM(CASE WHEN source = 'B' THEN cnt ELSE 0 END) - SUM(CASE WHEN source = 'A' THEN cnt ELSE 0 END)) AS difference
FROM (
    -- 统计表A每个entity_id的行数，标记来源为A
    SELECT entity_id, COUNT(*) AS cnt, 'A' AS source 
    FROM A 
    GROUP BY entity_id
    UNION ALL
    -- 统计表B每个entity_id的行数，标记来源为B
    SELECT entity_id, COUNT(*) AS cnt, 'B' AS source 
    FROM B 
    GROUP BY entity_id
) AS combined_stats
GROUP BY entity_id
-- 按差异值从大到小排序，取前10条；要前50条就改成LIMIT 50
ORDER BY difference DESC
LIMIT 10;

查询逻辑说明 #

1. 子查询阶段：分别对表A和表B做分组统计，得到每个entity_id的行数，同时给每组数据加上来源标记（'A'或'B'），用UNION ALL把两个统计结果合并成一个临时数据集。
2. 外层聚合阶段：对合并后的数据集按entity_id分组，用CASE语句分别求和表B和表A的行数，两者相减得到该entity_id的行数差异。
3. 排序与取数：最后按差异值降序排序，通过LIMIT控制返回前10或前50条结果，完全匹配你想要的输出格式。

额外说明 #

因为你已经给两张表的entity_id字段建了索引，GROUP BY entity_id的时候会自动用到索引，查询效率不会有问题。另外，即使某个entity_id只存在于表B中（表A没有对应数据），这个查询也能正确计算差异（此时差异就是表B中该entity_id的总行数），完全适配你提到的“表B行数始终大于等于表A”的场景。

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