SQL查询：统计零售商与分销商间销售聚合数量及去重求和问题

嘿，我来帮你搞定这个统计需求！你的核心问题是现有查询产生了重复行，没法正确求和，这是因为没抓住库存交易表的成对记录特性，也没做正确的分组筛选。咱们一步步来修正：

先明确需求核心 #

我们需要统计所有company_type为RETAILER或DISTRIBUTOR的公司，向同类型公司售出的商品总数量，最终按「卖家公司+卖家类型+买家类型」分组展示，甚至可以包含没有销售记录的卖家（显示数量为0）。

为什么你的现有查询有问题？ #

你的SQL把每条交易的卖家和买家部分拆成两个子查询再自连，但库存交易表中每笔实际交易是成对出现的（比如id6是BUY，id7是对应的SELL），同一条id的记录只会是BUY或SELL，这样的自连要么匹配错误，要么产生冗余数据，自然没法直接SUM。

解决方案一：只统计有实际销售的卖家 #

这个SQL会筛选出所有符合条件的销售记录，直接分组求和，结果只包含有销售行为的卖家：

SELECT
    s.id AS seller_company_id,
    s.name AS seller_company_name,
    s.company_type AS seller_company_type,
    SUM(t.quantity) AS total_quantity,
    b.company_type AS buyer_company_type
FROM inventory_transactions t
-- 关联卖家公司信息
JOIN companies s ON t.company_id = s.id
-- 关联买家公司信息
JOIN companies b ON t.buy_or_sell_to = b.id
WHERE
    -- 只选卖家的销售记录（成对记录里的SELL行）
    t.transaction_type = 'SELL'
    -- 卖家是目标类型
    AND s.company_type IN ('RETAILER', 'DISTRIBUTOR')
    -- 买家也是目标类型
    AND b.company_type IN ('RETAILER', 'DISTRIBUTOR')
-- 按卖家唯一标识、名称、类型，以及买家类型分组
GROUP BY
    s.id, s.name, s.company_type, b.company_type
ORDER BY
    s.id, b.company_type;

关键逻辑说明： #

• 只取transaction_type = 'SELL'的记录：因为库存交易表中，SELL行代表当前company_id是卖家，buy_or_sell_to是买家，正好对应我们要统计的销售行为。
• 两次关联companies表：分别获取卖家和买家的类型信息，确保双方都是目标类型。
• 分组求和：按卖家的唯一ID（避免同名公司混淆）、名称、类型，加上买家类型分组，这样每组的求和结果就是该卖家向对应类型买家销售的总数量。

解决方案二：显示所有目标类型卖家（含销售数量为0的） #

如果需要像你示例那样，即使卖家没有向某类买家销售，也显示数量为0，可以用CTE生成所有可能的卖家-买家类型组合，再左连接销售数据：

WITH all_sellers AS (
    -- 获取所有符合条件的卖家公司
    SELECT id, name, company_type
    FROM companies
    WHERE company_type IN ('RETAILER', 'DISTRIBUTOR')
),
buyer_types AS (
    -- 获取所有可能的买家类型（RETAILER/DISTRIBUTOR）
    SELECT DISTINCT company_type
    FROM companies
    WHERE company_type IN ('RETAILER', 'DISTRIBUTOR')
),
seller_buyer_pairs AS (
    -- 生成每个卖家对应两种买家类型的组合
    SELECT
        a.id AS seller_id,
        a.name AS seller_name,
        a.company_type AS seller_type,
        b.company_type AS buyer_type
    FROM all_sellers a
    CROSS JOIN buyer_types b
),
sales_summary AS (
    -- 统计实际销售数据
    SELECT
        t.company_id AS seller_id,
        b.company_type AS buyer_type,
        SUM(t.quantity) AS total_qty
    FROM inventory_transactions t
    JOIN companies b ON t.buy_or_sell_to = b.id
    WHERE
        t.transaction_type = 'SELL'
        AND EXISTS (
            SELECT 1 FROM companies s
            WHERE s.id = t.company_id
            AND s.company_type IN ('RETAILER', 'DISTRIBUTOR')
        )
        AND b.company_type IN ('RETAILER', 'DISTRIBUTOR')
    GROUP BY t.company_id, b.company_type
)
-- 组合所有可能的卖家-买家类型对，左连接实际销售数据，空值填0
SELECT
    sbp.seller_id AS seller_company_id,
    sbp.seller_name AS seller_company_name,
    sbp.seller_type AS seller_company_type,
    COALESCE(ss.total_qty, 0) AS total_quantity,
    sbp.buyer_type AS buyer_company_type
FROM seller_buyer_pairs sbp
LEFT JOIN sales_summary ss
    ON sbp.seller_id = ss.seller_id
    AND sbp.buyer_type = ss.buyer_type
ORDER BY
    sbp.seller_id, sbp.buyer_type;

关键逻辑说明： #

• 用CROSS JOIN生成所有卖家与买家类型的组合，确保每个卖家都有两条记录（对应向RETAILER和DISTRIBUTOR销售的情况）。
• 用COALESCE把没有销售记录的NULL替换为0，完美匹配你示例中的格式。

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