使用自连接或递归查询实现关联分组的订单计数需求

这是个典型的连通分量分组问题——咱们要把所有通过共享订单关联起来的ProductID归为一个组（比如A和B同属订单123，B又和C同属订单223，那A、B、C、D就属于同一个连通组），然后统计每组涉及的唯一订单数。下面用递归CTE（Common Table Expression）来实现，我会分步骤解释清楚：

实现思路 #

1. 用递归CTE找出每个ProductID能关联到的所有其他ProductID（通过共享订单的关系）；
2. 给每个连通的产品组分配唯一标识；
3. 拼接组内的所有ProductID，同时统计该组对应的唯一订单数量；
4. 合并结果得到期望的输出格式。

PostgreSQL 版本代码 #

WITH RECURSIVE product_connections AS (
    -- 锚点：每个产品先把自己作为初始关联节点
    SELECT 
        ProductID AS start_product,
        ProductID AS connected_product
    FROM orders
    GROUP BY ProductID
    
    UNION ALL
    
    -- 递归：找到和当前组内产品共享订单的其他产品，加入关联链
    SELECT 
        pc.start_product,
        o.ProductID AS connected_product
    FROM product_connections pc
    JOIN orders o1 ON pc.connected_product = o1.ProductID
    JOIN orders o ON o1.OrderID = o.OrderID
    WHERE o.ProductID NOT IN (
        SELECT connected_product FROM product_connections WHERE start_product = pc.start_product
    )
),
-- 给每个产品分配组ID：用组内最小的ProductID作为唯一标识，确保同组产品ID一致
product_groups AS (
    SELECT 
        connected_product,
        MIN(start_product) OVER (PARTITION BY connected_product) AS group_id
    FROM product_connections
),
-- 拼接组内所有ProductID，用|分隔
group_products AS (
    SELECT 
        group_id,
        STRING_AGG(DISTINCT connected_product, '|') AS ProductIds
    FROM product_groups
    GROUP BY group_id
),
-- 统计每个组对应的唯一订单数
group_order_counts AS (
    SELECT 
        pg.group_id,
        COUNT(DISTINCT o.OrderID) AS NoOfOrders
    FROM product_groups pg
    JOIN orders o ON pg.connected_product = o.ProductID
    GROUP BY pg.group_id
)
-- 最终合并结果
SELECT 
    gp.ProductIds,
    go.NoOfOrders
FROM group_products gp
JOIN group_order_counts go ON gp.group_id = go.group_id
ORDER BY go.NoOfOrders DESC;

MySQL 8.0+ 版本代码 #

如果用MySQL，只需要调整字符串拼接函数和存在性判断的写法：

WITH RECURSIVE product_connections AS (
    SELECT 
        ProductID AS start_product,
        ProductID AS connected_product
    FROM orders
    GROUP BY ProductID
    
    UNION ALL
    
    SELECT 
        pc.start_product,
        o.ProductID AS connected_product
    FROM product_connections pc
    JOIN orders o1 ON pc.connected_product = o1.ProductID
    JOIN orders o ON o1.OrderID = o.OrderID
    WHERE NOT EXISTS (
        SELECT 1 FROM product_connections 
        WHERE start_product = pc.start_product AND connected_product = o.ProductID
    )
),
product_groups AS (
    SELECT 
        connected_product,
        MIN(start_product) OVER (PARTITION BY connected_product) AS group_id
    FROM product_connections
),
group_products AS (
    SELECT 
        group_id,
        GROUP_CONCAT(DISTINCT connected_product ORDER BY connected_product SEPARATOR '|') AS ProductIds
    FROM product_groups
    GROUP BY group_id
),
group_order_counts AS (
    SELECT 
        pg.group_id,
        COUNT(DISTINCT o.OrderID) AS NoOfOrders
    FROM product_groups pg
    JOIN orders o ON pg.connected_product = o.ProductID
    GROUP BY pg.group_id
)
SELECT 
    gp.ProductIds,
    go.NoOfOrders
FROM group_products gp
JOIN group_order_counts go ON gp.group_id = go.group_id
ORDER BY go.NoOfOrders DESC;

关键细节说明 #

• 递归CTE的作用是遍历所有通过订单关联的产品链，确保没有遗漏任何连通的ProductID；
• 用MIN(start_product)作为组ID，是为了保证同一个连通组的所有产品都有相同的组标识，避免重复分组；
• 统计订单数时用COUNT(DISTINCT OrderID)，防止同一订单被多次统计（比如订单123同时包含A和B，只算1次）。

内容的提问来源于stack exchange，提问作者s-a-n