使用CTE的结果更新另一个表

Question

我有以下CTE可以做我想做的事情。

;WITH numbering AS 
(
    SELECT SrcID, AsOfDate, PID,
           dense_rank() OVER (PARTITION BY SrcID ORDER BY PID) AS rowno
    FROM   RAW_DATA
)
SELECT SrcID,
       MAX(CASE rowno WHEN 1 THEN PID END) AS PID1,
       MAX(CASE rowno WHEN 2 THEN PID END) AS PID2,
       MAX(CASE rowno WHEN 3 THEN PID END) AS PID3,
       MAX(CASE rowno WHEN 4 THEN PID END) AS PID4
FROM   numbering
GROUP BY SrcID

我需要使用它，以及ASOFDATE和PID，但是我不想将其显示在CTE中，因为这会丢弃填充PID1，PID2，PID3和PID4。。。所有这些都是正确的。我确实需要SRCID和ASOFATE来对另一个名为" RAW_DATA"的表进行更新。如何运行CTE生成所需的特定数据集，然后根据SRCID和ASOFDATE之间的连接进行更新对RAW_DATA表？

我认为应该是这样的：

    ;WITH numbering AS 
    (
        SELECT SrcID, AsOfDate, PID,
               dense_rank() OVER (PARTITION BY SrcID ORDER BY PID) AS rowno
        FROM   RAW_DATA
    )
    SELECT SrcID,
           MAX(CASE rowno WHEN 1 THEN PID END) AS PID1,
           MAX(CASE rowno WHEN 2 THEN PID END) AS PID2,
           MAX(CASE rowno WHEN 3 THEN PID END) AS PID3,
           MAX(CASE rowno WHEN 4 THEN PID END) AS PID4
    FROM   numbering
    GROUP BY SrcID
    
INSERT INTO RAW_DATA(SrcID, AsOfDate, PID, PID1, PID2, PID3, PID4)
Select *
FROM         RAW_DATA INNER JOIN numbering 
             ON RAW_DATA.SrcID = numbering.SrcID 
             AND RAW_DATA.AsofDate = numbering.AsofDate

但是，这引发了此错误：无效的对象名称'编号'。我在SQL Server 2008上。

更新

在这里修改我的原始帖子。

杰弗里，我正在测试您的解决方案：

--drop table Count_Unique_PID
;WITH numbering AS 
    (
        SELECT SrcID, AsOfDate, PID,PID1,PID2,PID3,PID4,
               dense_rank() OVER (PARTITION BY AsOfDate, SrcID ORDER BY PID) AS rowno
        FROM   RAW_DATA
    )
    SELECT SrcID,AsOfDate, PID,
           MAX(CASE rowno WHEN 1 THEN PID END) AS PID1,
           MAX(CASE rowno WHEN 2 THEN PID END) AS PID2,
           MAX(CASE rowno WHEN 3 THEN PID END) AS PID3,
           MAX(CASE rowno WHEN 4 THEN PID END) AS PID4
    INTO Count_Unique_PID
    FROM   numbering
    GROUP BY SrcID,AsOfDate, PID
    SELECT SrcID,
           AsOfDate,
           PID,
           PID1,
           PID2,
           PID3,
           PID4
    FROM   Count_Unique_PID
    GROUP BY SrcID,AsOfDate, PID,PID1,PID2,PID3,PID4

UPDATE    RAW_DATA
SET              PID1 = B.PID1, 
                 PID2 = B.PID2,
                 PID3 = B.PID3,
                 PID4 = B.PID4
FROM         RAW_DATA AS A INNER JOIN Count_Unique_PID  As B
             ON A.SrcID = B.SrcID 
             AND A.AsofDate = B.AsofDate

这是运行的，但它将我的行从357,518增加到724,150。记录的数量应保持不变；更新完成后应该保持357,518 ...这里仍然不太正确。也许我在某个地方或类似的地方错过了一个小组。我看不到实际问题是什么。对此有其他想法吗？

Answer 1

ctes只能通过立即遵循的语句引用。如果稍后需要结果，则可以将CTE插入临时表中，然后从该临时表中进行选择，然后从该临时表中插入：

;WITH numbering AS 
    (
        SELECT SrcID, AsOfDate, PID,
               dense_rank() OVER (PARTITION BY SrcID ORDER BY PID) AS rowno
        FROM   RAW_DATA
    )
    SELECT SrcID,
           MAX(CASE rowno WHEN 1 THEN PID END) AS PID1,
           MAX(CASE rowno WHEN 2 THEN PID END) AS PID2,
           MAX(CASE rowno WHEN 3 THEN PID END) AS PID3,
           MAX(CASE rowno WHEN 4 THEN PID END) AS PID4
    INTO #tmp
    FROM   numbering
    GROUP BY SrcID
    SELECT SrcID,
           PID1,
           PID2,
           PID3,
           PID4
    FROM   #tmp
    GROUP BY SrcID
INSERT INTO RAW_DATA(SrcID, AsOfDate, PID, PID1, PID2, PID3, PID4)
Select *
FROM         RAW_DATA INNER JOIN #tmp
             ON RAW_DATA.SrcID = #tmp.SrcID 
             AND RAW_DATA.AsofDate = #tmp.AsofDate

Answer 2

您需要将查询封装为第二个CTE：

sintaxis是

WITH cte1 as (
   SELECT ...
), cte2  as (
   SELECT *
   FROM cte1
   .....
)
INSERT INTO table_name
SELECT * 
FROM cte2

Answer 3

您的选择语句无助于插入（也许是用于验证）。只需删除选择语句，您的插入应起作用。

;WITH numbering AS 
(
    SELECT SrcID, AsOfDate, PID,
           dense_rank() OVER (PARTITION BY SrcID ORDER BY PID) AS rowno
    FROM   RAW_DATA
)

INSERT INTO RAW_DATA(SrcID, AsOfDate, PID, PID1, PID2, PID3, PID4)
Select *
FROM         
         RAW_DATA INNER JOIN numbering 
         ON RAW_DATA.SrcID = numbering.SrcID 
         AND RAW_DATA.AsofDate = numbering.AsofDate

如果您还需要在CTE上运行选择，只要查询正在运行，就应该使用临时表。