SQL技术问题：如何按PO单号+承运商分组生成对应XML跟踪号列表

嘿，我来帮你搞定这个XML分组的需求！你要的是同时按PO单号（ponumber）和承运商（carrier）双层分组，让每个承运商对应的alltrackingno只包含该承运商的跟踪号对吧？这里给你一个可行的写法，基于SQL的FOR XML语法实现：

示例SQL代码 #

假设你的数据源表名为shipments，包含字段ponumber、ca_type、trackingno，可以这样写：

SELECT 
    ponumber,
    -- 内层生成每个PO下的承运商分组XML
    (
        SELECT 
            carrier,
            -- 生成当前承运商对应的跟踪号列表XML
            (
                SELECT trackingno AS 'trackingno'
                FROM shipments inner_track
                WHERE inner_track.ponumber = outer_po.ponumber
                  AND inner_track.carrier = carrier_group.carrier
                FOR XML PATH(''), ROOT('alltrackingno'), TYPE
            )
        FROM (
            -- 先按PO+承运商分组，确定每个PO下的承运商集合
            SELECT 
                ponumber,
                CASE 
                    WHEN ca_type = 'FEX' THEN 'fedex'
                    WHEN ca_type = 'UPS' THEN 'ups'
                    ELSE 'other_carrier' -- 这里替换你实际的默认值
                END AS carrier
            FROM shipments
            GROUP BY ponumber,
                CASE 
                    WHEN ca_type = 'FEX' THEN 'fedex'
                    WHEN ca_type = 'UPS' THEN 'ups'
                    ELSE 'other_carrier'
                END
        ) AS carrier_group
        WHERE carrier_group.ponumber = outer_po.ponumber
        FOR XML PATH('carrier'), TYPE
    ) AS carrier_details
FROM (
    -- 外层按PO单号去重，确保每个PO只出现一次
    SELECT DISTINCT ponumber
    FROM shipments
) AS outer_po
FOR XML PATH('po'), ROOT('po_shipments')

代码说明 #

1. 最内层子查询：针对指定PO和承运商，筛选对应的跟踪号，生成<alltrackingno>节点包裹的跟踪号列表
2. 中间层子查询：先按ponumber+carrier分组，确定每个PO下存在哪些承运商，再关联内层查询生成每个承运商的跟踪号XML
3. 最外层查询：按PO单号去重，把每个PO下的所有承运商分组XML整合起来，最终生成完整的双层分组结构

效果示例 #

假设你的数据是：

最终生成的XML会是：

<po_shipments>
  <po>
    <ponumber>PO001</ponumber>
    <carrier_details>
      <carrier>
        <carrier>fedex</carrier>
        <alltrackingno>
          <trackingno>123456</trackingno>
        </alltrackingno>
      </carrier>
      <carrier>
        <carrier>ups</carrier>
        <alltrackingno>
          <trackingno>ups987653</trackingno>
        </alltrackingno>
      </carrier>
    </carrier_details>
  </po>
  <po>
    <ponumber>PO002</ponumber>
    <carrier_details>
      <carrier>
        <carrier>fedex</carrier>
        <alltrackingno>
          <trackingno>789012</trackingno>
        </alltrackingno>
      </carrier>
    </carrier_details>
  </po>
</po_shipments>

这样就完美实现了你要的：每个PO下按承运商分组，每个承运商的alltrackingno只包含自身的跟踪号。

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