如何编写复杂SQL查询实现包裹/货运/箱的分组与关联输出

前提假设 #

假设输入表名为package_shipment_box，字段包括：

• package_id：包裹编号
• shipment_id：货运编号
• box_id：箱编号

核心逻辑：箱的分组规则 #

两个箱属于同一组的条件是：通过包裹-货运的关联形成连通关系（比如箱A和货运S1关联，箱B也和S1关联；或者箱B和货运S2关联，箱C和S2关联，那么A、B、C同属一组）。这是典型的图的连通分量问题，用递归CTE实现。

输出1：原表新增分组编号与分组名称 #

先通过递归CTE生成每个箱的所属分组，再关联回原表：

WITH box_connections AS (
    -- 找出所有通过货运关联的箱对（去重避免重复）
    SELECT DISTINCT
        b1.box_id AS box_a,
        b2.box_id AS box_b
    FROM package_shipment_box b1
    JOIN package_shipment_box b2
        ON b1.shipment_id = b2.shipment_id
        AND b1.box_id <> b2.box_id
),
recursive_groups AS (
    -- 递归起始：每个箱自身作为初始节点
    SELECT
        box_id AS current_box,
        box_id AS group_leader,
        CAST(box_id AS VARCHAR) AS group_members
    FROM (SELECT DISTINCT box_id FROM package_shipment_box) AS unique_boxes
    UNION ALL
    -- 递归遍历：合并连通的箱
    SELECT
        rg.current_box,
        LEAST(rg.group_leader, bc.box_b) AS group_leader,
        CONCAT(rg.group_members, ', ', bc.box_b) AS group_members
    FROM recursive_groups rg
    JOIN box_connections bc
        ON rg.current_box = bc.box_a
    WHERE NOT rg.group_members LIKE CONCAT('%', bc.box_b, '%')
),
final_groups AS (
    -- 取每个箱的最终分组（最小的group_leader作为组编号，组名称用所有成员拼接）
    SELECT
        current_box AS box_id,
        MIN(group_leader) AS group_number,
        STRING_AGG(DISTINCT group_members, ', ') AS group_name
    FROM recursive_groups
    GROUP BY current_box
)
-- 关联原表得到输出1
SELECT
    psb.package_id,
    psb.shipment_id,
    psb.box_id,
    fg.group_number,
    fg.group_name
FROM package_shipment_box psb
JOIN final_groups fg ON psb.box_id = fg.box_id
ORDER BY psb.package_id;

输出2：按箱分组展示所属组及关联箱 #

基于上面的分组逻辑，处理关联箱的展示：

WITH box_connections AS (
    SELECT DISTINCT
        b1.box_id AS box_a,
        b2.box_id AS box_b
    FROM package_shipment_box b1
    JOIN package_shipment_box b2
        ON b1.shipment_id = b2.shipment_id
        AND b1.box_id <> b2.box_id
),
recursive_groups AS (
    SELECT
        box_id AS current_box,
        box_id AS group_leader,
        CAST(box_id AS VARCHAR) AS group_members
    FROM (SELECT DISTINCT box_id FROM package_shipment_box) AS unique_boxes
    UNION ALL
    SELECT
        rg.current_box,
        LEAST(rg.group_leader, bc.box_b) AS group_leader,
        CONCAT(rg.group_members, ', ', bc.box_b) AS group_members
    FROM recursive_groups rg
    JOIN box_connections bc
        ON rg.current_box = bc.box_a
    WHERE NOT rg.group_members LIKE CONCAT('%', bc.box_b, '%')
),
final_groups AS (
    SELECT
        current_box AS box_id,
        MIN(group_leader) AS group_number
    FROM recursive_groups
    GROUP BY current_box
),
group_details AS (
    -- 聚合同组所有箱
    SELECT
        fg.group_number,
        STRING_AGG(DISTINCT fg2.box_id, ', ') AS group_name
    FROM final_groups fg
    JOIN final_groups fg2 ON fg.group_number = fg2.group_number
    GROUP BY fg.group_number
)
-- 生成输出2：每个箱的所属组和关联其他箱
SELECT
    fg.box_id,
    fg.group_number,
    gd.group_name,
    -- 去掉自身，得到关联的其他箱
    CASE
        WHEN gd.group_name LIKE CONCAT(fg.box_id, ', %') THEN SUBSTRING(gd.group_name FROM LENGTH(fg.box_id) + 3)
        WHEN gd.group_name LIKE CONCAT('%, ', fg.box_id) THEN SUBSTRING(gd.group_name FROM 1 FOR LENGTH(gd.group_name) - LENGTH(fg.box_id) - 2)
        ELSE ''
    END AS related_boxes
FROM final_groups fg
JOIN group_details gd ON fg.group_number = gd.group_number
ORDER BY fg.box_id;

补充说明 #

1. 递归CTE负责遍历所有连通的箱，用组内最小的箱编号作为group_number，保证分组唯一。
2. group_name可根据需求调整格式，比如用数组或特定分隔符拼接。
3. 输出2中related_boxes通过字符串截取去掉当前箱，不同SQL方言可替换为更高效的数组操作（比如PostgreSQL的array_remove）。

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