SQL关联聚合查询问题：避免连接乘法效应，获取正确统计结果

这个坑我踩过好几次！你遇到的问题就是典型的连接乘法效应：当同一个商品在销售表（SALES）和库存表（WAREHOUSE）里都有多条记录时，直接LEFT JOIN会产生笛卡尔积（比如商品A在SALES里有2条，WAREHOUSE里有3条，join后会变成2×3=6条记录），这时候直接COUNT就会把这些重复组合都算进去，结果肯定不对。

最优实现方法：先聚合再关联 #

最清晰也最高效的方式是先分别对两个表按商品维度聚合统计，再将统计结果关联。这样从根源上避免了笛卡尔积，性能也更好（因为聚合后的数据量远小于原始表）。

具体SQL代码如下：

SELECT 
    sales_stats.Item,
    sales_stats.count_period,
    COALESCE(warehouse_stats.current_warehouse, '') AS current_warehouse
FROM (
    -- 第一步：统计2019年各商品的销售数量
    SELECT 
        Item,
        COUNT(Item) AS count_period
    FROM SALES
    WHERE Period = 2019
    GROUP BY Item
) AS sales_stats
LEFT JOIN (
    -- 第二步：统计仓库中各商品的库存数量
    SELECT 
        Item,
        COUNT(SerialN) AS current_warehouse
    FROM WAREHOUSE
    GROUP BY Item
) AS warehouse_stats ON sales_stats.Item = warehouse_stats.Item;

这段代码的逻辑很直观：

1. 先从SALES表中筛选2019年的数据，按Item分组统计销量，得到sales_stats子查询结果；
2. 再从WAREHOUSE表中按Item分组统计库存数，得到warehouse_stats子查询结果；
3. 最后用LEFT JOIN把两个统计结果关联起来，用COALESCE把NULL值转为空字符串，和你期望的结果完全匹配。

简洁但需权衡性能的替代方案 #

如果追求代码更紧凑，在数据量不大的场景下，可以用COUNT(DISTINCT)来消除笛卡尔积带来的重复计数：

SELECT 
    a.Item,
    COUNT(DISTINCT a.Item || a.Period || ROWID) AS count_period, -- 用唯一标识避免重复计数（如果有主键直接用主键）
    COUNT(DISTINCT b.SerialN) AS current_warehouse
FROM SALES a
LEFT JOIN WAREHOUSE b ON a.Item = b.Item
WHERE a.Period = 2019
GROUP BY a.Item;

不过要注意：这种方式是先产生笛卡尔积再去重计数，当数据量较大时，性能会比先聚合再关联差很多，所以优先推荐第一种方案。

最终结果验证 #

执行第一种方案的SQL后，会得到你期望的结果：

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