如何通过子查询转换头行-明细行格式的SQL临时表？

现有临时表#Tbl采用头行-明细行格式：Rw_Type为'D'的行是头行，紧随其后的Rw_Type为'M'的行属于该头行的明细，直到下一个'D'头行出现。表结构及测试数据如下：

CREATE TABLE #Tbl
(
  Serial VARCHAR(100),
  Rw INT ,
  Rw_Type VARCHAR(1),
  Decrip_Article  VARCHAR(max),
  Is_Article VARCHAR(1)
)

INSERT INTO #Tbl ( Serial, Rw, Rw_Type, Decrip_Article, Is_Article)
VALUES( '0000265954',   '30',   'D',    'ORD.2023217BaltiniBAL7700070861','.' ),                  
('0000265954',  '40',   'M',    '36200 - JEANS size : 4 color :0224','*'  ),                  
('0000265954',  '60',   'D',    'ORD.2023217BaltiniBAL7700070898','.'   ),                 
('0000265954',  '70',   'M',    'AAUBT0002FA01 - BELT size : 01 color :GR','*'   ),         
('0000265954',  '70',   'M',    'AAUBT0002FA01 - BELT size : 05 color :RR','*'  ),              
('0000265954',  '90',   'D' ,'ORD.2023217BaltiniBAL7700070887 ' ,   '.'    ),               
('0000265954',  '100',  'M' ,'PERGSA049CR0010 - SHOULDERBAG size : 01 ','*'  ), 
('0000265954',  '100',  'M' ,'PERGSA049CR0010 - SHOULDERBAG size : 02 ','*' ),  
('0000265954',  '100',  'M' ,'PERGSA049CR0010 - SHOULDERBAG size : 03 ','*'  );

我尝试用子查询实现关联，但未成功，代码如下：

select e.Serial, e.Rw,(
    select min(e1.Rw) from 
        #tbl e1 
        where e1.Serial= e.Serialand 
        e1.Type_Row = 'D' and 
        e1.Rw> e.Rw 
     ) AS c
    from #Tbl e 
    where e.Type_Row = 'M'

解决方案 #

可以利用窗口函数MAX() OVER()为每个明细行匹配最近的头行，具体实现如下：

WITH HeadGroup AS (
    SELECT 
        *,
        -- 为每行标记所属的最近头行的Rw值
        MAX(CASE WHEN Rw_Type = 'D' THEN Rw END) OVER (PARTITION BY Serial ORDER BY Rw) AS Head_Rw
    FROM #Tbl
)
-- 关联头行与明细行，输出关联结果
SELECT 
    h.Serial,
    h.Head_Rw AS Head_Row_Number,
    h_head.Decrip_Article AS Head_Description,
    h.Rw AS Detail_Row_Number,
    h.Decrip_Article AS Detail_Description,
    h.Is_Article
FROM HeadGroup h
JOIN #Tbl h_head 
    ON h.Serial = h_head.Serial 
    AND h.Head_Rw = h_head.Rw 
    AND h_head.Rw_Type = 'D'
WHERE h.Rw_Type = 'M'
ORDER BY h.Serial, h.Head_Rw, h.Rw;

说明 #

1. CTE分组标记：通过MAX() OVER(PARTITION BY Serial ORDER BY Rw)，按Serial分组、Rw排序，为每行找到之前最近的D类型头行的Rw值，作为该明细行的归属头行标识。
2. 关联头行信息：将标记后的表与原表关联，取出头行的描述信息，筛选出明细行后得到最终的关联结果。
3. 原代码问题：你写的子查询中存在字段名错误（Type_Row应为Rw_Type），且逻辑方向错误（找大于当前Rw的D行，实际需要找小于当前Rw的最近D行）。

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