MySQL关联两表查询时如何避免COUNT聚合统计结果异常

直接在两张表的原始明细层用unique_id做内连接后再做聚合计算，关联逻辑会先过滤掉表B中不匹配表Aid=1筛选条件的记录，最终COUNT(date)只能统计到关联成功的销售天数，无法覆盖门店全量符合要求的销售记录，导致统计值偏小。

不要在原始明细行维度做关联，先独立完成两个聚合逻辑的计算，再做结果拼接，从根本上避免关联条件缩小聚合统计范围：

• 第一步：从表A筛选指定id，按unique_id分组计算对应商品的平均售价
• 第二步：从表B按业务筛选条件过滤，按Store分组，独立计算每个门店的总销售天数、平均成本
• 第三步：先关联表A聚合结果和表B明细拿到目标商品对应的售卖门店，去重后再和表B的门店聚合结果关联，得到最终数据

-- 计算指定id对应商品的平均售价
WITH a_product AS (
    SELECT 
        Unique_id,
        AVG(price) AS avg_price
    FROM table_a
    WHERE id = 1
    GROUP BY Unique_id
),
-- 拿到目标商品对应的所有售卖门店，去重避免重复计算
target_stores AS (
    SELECT DISTINCT
        a.avg_price,
        b.Store
    FROM a_product a
    INNER JOIN table_b b ON a.Unique_id = b.Unique_id
),
-- 独立统计每个门店符合要求的全量销售数据
store_stat AS (
    SELECT
        Store,
        COUNT(Date) AS count_days,
        AVG(Cost) AS avg_cost
    FROM table_b
    -- 若需要统计全品类销售数据，去掉下面的品类筛选条件即可匹配你给出的预期结果
    -- WHERE Category = 'Shoes'
    GROUP BY Store
)
-- 拼接最终结果
SELECT
    ts.Store,
    ROUND(ts.avg_price, 2) AS `AVG price`,
    ss.count_days AS `COUNT days`,
    ROUND(ss.avg_cost, 2) AS `AVG cost`
FROM target_stores ts
INNER JOIN store_stat ss ON ts.Store = ss.Store;

和你预期的输出完全一致：

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