SQL Server遍历Select结果 批量标记BOM套件关联行数据

现有SQL逻辑 #

现有逻辑已生成#bom_mn、#kit两个临时表，原有SQL代码如下：

-- get make/normal BOMs...
select b.TopLevelItemNumber as TopLevel
, case
  when b.InEffectivityDate > getdate() or b.OutEffectivityDate < getdate() then 'Not In Effect'
  else replicate('.', b.BillLevel) + cast(b.BillLevel as varchar(9)) end Bill_Level
, b.ParentItemNumber as Parent, b.ParentItemDescription Parent_Desc, b.PointOfUseID as POU, b.OperationSequenceNumber as Seq
, b.ComponentItemNumber as Component, b.ComponentItemDescription as CompDesc
, b.ComponentItemUM as UoM, b.ComponentItemMakeBuyCode as MB, b.ComponentType as CT, b.RequiredQuantity as Reqd_Qty, b.QuantityType as QT
, b.LeadTimeOffsetDays as LTO, b.ConfigurationGroup as OptGrp, b.ConfigurationType as OptTyp
, case
  when b.ComponentType in ('R') then ''
  when b.BackflushStockroom != '' then b.BackflushStockroom + ' ~ ' + b.BackflushBin
  when i2.PreferredStockroom != '' then i2.PreferredStockroom + ' ~ ' + i2.PreferredBin
  else 'WIP' end Orig_Loc
, case
  when b.ComponentType in ('R') then ''
  when b.BackflushStockroom != '' then 'BackFlushed' when i2.PreferredStockroom != '' then 'Picked'
  else '' end Issue_Via
, case when i1.PreferredStockroom != '' then i1.PreferredStockroom + ' ~ ' + i1.PreferredBin else 'WIP' end Dest_Loc
, b.BillLevelSort
, @timestamp as timestamp
into #bom_mn
from _NoLock_FS_MultiLevelBillAll b
 left join _NoLock_FS_Item i1 on b.ParentItemKey = i1.ItemKey
 left join _NoLock_FS_Item i2 on b.ComponentItemKey = i2.ItemKey
where b.TopLevelItemNumber in (select * from #mn)
order by b.BillLevelSort

-- combine and identify kit items...
select row_number() over(partition by b.TopLevel order by b.BillLevelSort) + .1 as row
, case when b.Parent in (select * from #mn) then 'Kit' else m.TopLevel + '~' + m.Parent + '~' + m.Component end Kit
, b.*
into #kit
from #bom b left join #bom_mn m on b.Parent = m.Parent and b.Component = m.Component
order by b.BillLevelSort

需求说明 #

需要对#kit表的查询结果新增TEST列，规则如下：

• 当行的CT字段值为N时，TEST列标记为Start of Kit
• 该标记行之后的所有行，TEST列标记为Part of Kit
• 直到遇到Bill_Level列值后缀为.1的行时，停止标记为Part of Kit
  效果示意图：
  

实现代码 #

无需使用循环，直接用SQL窗口函数实现集合操作，性能远高于循环遍历，替换原有最后的查询语句即可：

WITH mark_data AS (
    SELECT 
        *,
        -- 标记Kit起始行
        CASE WHEN CT = 'N' THEN 1 ELSE 0 END AS is_start,
        -- 标记终止行（Bill_Level后缀为.1）
        CASE WHEN Bill_Level LIKE '%.1' THEN 1 ELSE 0 END AS is_end,
        -- 按排序规则生成唯一行号，确保分组顺序正确
        ROW_NUMBER() OVER (ORDER BY BillLevelSort) AS rn
    FROM #kit
    WHERE MB = 'M'
),
group_data AS (
    SELECT 
        *,
        -- 累计求和做分组：遇到起始行或终止行时分组编号+1
        SUM(CASE WHEN is_start = 1 OR is_end = 1 THEN 1 ELSE 0 END) OVER (ORDER BY rn) AS kit_group
    FROM mark_data
)
SELECT 
    row,
    Bill_Level,
    CT,
    Component,
    CompDesc,
    CASE 
        WHEN is_start = 1 THEN 'Start of Kit'
        -- 同组内有起始行、且当前不是终止行的标记为Part of Kit
        WHEN MAX(is_start) OVER (PARTITION BY kit_group) = 1 AND is_end = 0 THEN 'Part of Kit'
        ELSE '' 
    END AS TEST
FROM group_data
ORDER BY BillLevelSort

逻辑说明 #

• 第一步先标记出所有Kit起始行和终止行
• 第二步通过累计求和将起始行到下一个终止行之间的所有行划到同一个分组
• 第三步同一分组内只要存在起始行，组内除了起始行和终止行之外的所有行都标记为Part of Kit
  如果需要把TEST列更新到#kit表，可在生成#kit表之后执行UPDATE语句即可，逻辑和上述查询一致。

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