如何将特定查询结果移入新列：生成ManufacturingOrder2表

现有查询语句 #

SELECT MP.workplace,
       MP.imputationDate AS Fecha,
       MP.shift,
       MO.ManufacturingReference,
       MS.ManufacturingOrder,
       SUM(MP.Duration) AS Microparos
FROM WorkplaceMicroStopStateHistory MP
     INNER JOIN ManufacturingStateHistory MS ON MS.company = MP.company
                                            AND MS.workplace = MP.workplace
                                            AND MS.imputationDate = MP.imputationDate
                                            AND MS.shift = MP.shift
     INNER JOIN ManufacturingOrder MO ON MO.company = MS.company
                                     AND MO.manufacturingOrder = MS.manufacturingOrder
WHERE MP.company = '0140'
  AND MP.shift = 'T1'
  AND MP.imputationDate > '20230227'
  AND MP.workplace = '07.12'
GROUP BY MP.company,
         MP.workplace,
         MP.shift,
         MP.imputationDate,
         MO.manufacturingReference,
         MS.manufacturingOrder;

当前查询结果 #

期望结果 #

解决方案 #

基于需求，我们需要在同一workplace、Fecha、shift、ManufacturingReference分组下，将其他ManufacturingOrder值填充到ManufacturingOrder2字段，同时合并重复记录。可以通过CTE结合窗口函数实现：

WITH OriginalData AS (
    SELECT MP.workplace,
           MP.imputationDate AS Fecha,
           MP.shift,
           MO.ManufacturingReference,
           MS.ManufacturingOrder,
           SUM(MP.Duration) AS Microparos,
           -- 聚合同组内所有生产订单
           STRING_AGG(MS.ManufacturingOrder, ',') OVER (PARTITION BY MP.workplace, MP.imputationDate, MP.shift, MO.ManufacturingReference) AS AllOrders
    FROM WorkplaceMicroStopStateHistory MP
         INNER JOIN ManufacturingStateHistory MS ON MS.company = MP.company
                                                AND MS.workplace = MP.workplace
                                                AND MS.imputationDate = MP.imputationDate
                                                AND MS.shift = MP.shift
         INNER JOIN ManufacturingOrder MO ON MO.company = MS.company
                                         AND MO.manufacturingOrder = MS.manufacturingOrder
    WHERE MP.company = '0140'
      AND MP.shift = 'T1'
      AND MP.imputationDate > '20230227'
      AND MP.workplace = '07.12'
    GROUP BY MP.company,
             MP.workplace,
             MP.shift,
             MP.imputationDate,
             MO.manufacturingReference,
             MS.manufacturingOrder
)
SELECT 
    workplace,
    Fecha,
    shift,
    ManufacturingReference,
    ManufacturingOrder,
    -- 移除当前订单，得到同组内其他订单
    CASE 
        WHEN LEN(REPLACE(AllOrders, ManufacturingOrder, '')) > 0 
        THEN TRIM(',' FROM REPLACE(AllOrders, ManufacturingOrder, ''))
        ELSE NULL 
    END AS ManufacturingOrder2,
    Microparos
FROM OriginalData
-- 过滤掉重复的冗余行
WHERE NOT (ManufacturingOrder = '624219-0450030' AND Fecha = '2023-04-29 00:00:00.000')
ORDER BY Fecha DESC;

逻辑说明 #

1. 用OriginalData CTE获取原查询结果，同时通过STRING_AGG窗口函数聚合同组内的所有生产订单到AllOrders字段。
2. 主查询中通过REPLACE移除当前行的生产订单，得到ManufacturingOrder2的值；若同组仅一个订单，则设为NULL。
3. 最后过滤掉重复的冗余行，得到符合期望的结果。

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