复杂SQL MERGE/PIVOT不按所需排序

Question

我有以下两个表格; ICD使用

CREATE TABLE ICD ([E] VARCHAR(8), [I] VARCHAR(8)); 
GO 
INSERT INTO ICD ([E], [I]) 
VALUES 
    ('1', 'B1'), 
    ('1', 'A1'), 
    ('1', 'C23q'), 
    ('1', '32Dq'), 
    ('2', 'FFFq'), 
    ('2', 'ERRE'), 
    ('2', 'E'), 
    ('3', 'WQWW'), 
    ('3', 'WQ'), 
    ('4', 'AAAA'); 
GO 

其中O是给在哪一列I将被插入到另一个表中的顺序创建。第二个表是Episode，这将创建使用

CREATE TABLE Episode ([EpiNum] VARCHAR(4), [SomeField] VARCHAR(4), 
         [DX1] VARCHAR(10), [DX2] VARCHAR(10), 
         [DX3] VARCHAR(10), [DX4] VARCHAR(10)); 
GO 
INSERT INTO Episode ([EpiNum], [SomeField], [DX1], [DX2], [DX3], [DX4]) 
VALUES 
    ('1', 'Test', '', '', '', ''), 
    ('2', 'Test', '', '', '', ''), 
    ('3', 'Test', '', '', '', ''); 
GO 

所以我的目标是在ICD取决于是否有插入值上插入来自I列中的值到DXn领域。忍受着我;我一直在使用

MERGE INTO [Episode] AS T 
USING (
    SELECT [EpiNum], [1], [2], [3], [4] 
    FROM 
     ( 
      SELECT *, ROW_NUMBER() 
      OVER (PARTITION BY [EpiNum] ORDER BY [I]) AS rn 
      FROM [ICD] 
    ) AS p 
    PIVOT 
    (
     MAX([I]) 
     FOR rn IN ([1], [2], [3], [4])) AS p2 
    ) AS S 
ON T.[EpiNum] = S.[EpiNum] 
WHEN MATCHED THEN 
    UPDATE SET [DX1] = S.[1], [DX2] = S.[2], [DX3] = S.[3], [DX4] = S.[4]; 

将会产生一个Episode表看起来像

EpiNum Test DX1  DX2  DX3  DX4 
'1'  'Test' '32Dq' 'A1' 'B2' 'C23q' 
'2'  'Test' 'E'  'ERRE' 'FFFq' null 
'3'  'Test' 'WQ' 'WQWW' null null 
'4'  'Test' 'AAAA' null null null 

大这个工作！等等......不是很好，PIVOT/MERGE中的ORDER BY正在破坏数据的原始顺序。我试图通过添加另一列O持有我希望为了解决这个问题，所以ICD由

CREATE TABLE ICD ([E] VARCHAR(8), [I] VARCHAR(8), [O] INT); 
GO 
INSERT INTO ICD ([E], [I], [O]) 
VALUES 
    ('1', 'B1.2', 1), 
    ('1', 'A1.2', 2), 
    ('1', 'C23q', 3), 
    ('1', '32Dq', 4), 
    ('2', 'FFFq', 1), 
    ('2', 'ERRE', 2), 
    ('3', 'WQWW', 1), 
    ('4', 'AAAA', 1); 
GO 

定义，我试图ORDER BY列O使用

MERGE INTO [Episode] AS T 
USING (
    SELECT [E], [1], [2], [3], [4] 
    FROM 
     ( 
      SELECT *, ROW_NUMBER() 
       OVER (PARTITION BY [E] ORDER BY [O]) AS rn 
      FROM [ICD] 
     ) AS p 
    PIVOT 
    (
     MAX([I]) 
     FOR rn IN ([1], [2], [3], [4])) AS p2 
    ) AS S 
ON T.[E] = S.[E] 
WHEN MATCHED THEN 
    UPDATE SET [DX1] = S.[1], [DX2] = S.[2], [DX3] = S.[3], [DX4] = S.[4]; 

，但我得到以下消息

MERGE语句试图更新或删除同一行多次。当目标行匹配多个源行时会发生这种情况。 MERGE语句不能多次更新/删除目标表的同一行。细化ON子句以确保目标行至多与一个源行匹配，或使用GROUP BY子句对源行进行分组。

如何修正上面的MERGE/PIVOT以正确订购？

感谢您的时间

Answer 1

嗯，你的例子并不正确运行，因为你是在创建表和查询之间的混合列[E]和Epinum。但假设这些列实际上是相同的列，那么您的第二个查询不起作用，因为您实际上并未在[E]列上进行分组。试试这个：

MERGE INTO [Episode] AS T 
USING (
    SELECT [E], [1], [2], [3], [4] 
    FROM 
     ( 
      SELECT [E], [I], ROW_NUMBER() 
       OVER (PARTITION BY [E] ORDER BY [O]) AS rn 
      FROM [ICD] 
     ) AS p 
    PIVOT 
    (
     MAX([I]) 
     FOR rn IN ([1], [2], [3], [4])) AS p2 
    ) AS S 
ON T.[Epinum] = S.[E] 
WHEN MATCHED THEN 
    UPDATE SET [DX1] = S.[1], [DX2] = S.[2], [DX3] = S.[3], [DX4] = S.[4]; 

现在，我真的不明白，为什么你正在使用ROW_NUMBER()，而不是简单地使用[O]列（价值观似乎我也一样），但是这仍然可以正常工作。