sql - 编写 CTE 查询记录重复且顺序错误

Question

我有一个表格，其中包含行中的数据，并且需要在列中汇总数据。目前数据确实在列中，但数据重复并且顺序错误。此外，当我输入多个服务器名称时，我将获得服务器 1 列，其中信息服务器 2 列将为空，服务器 3 列将包含数据。

我在 stackoverflow、social.msdn.microsoft.com、sqlservercentral.com 和 sqlshack.com 上查看了答案

WITH 
cte (Captivate_Id, Building_Name ,Server_Name,Server_Number)
as

   (
     SELECT b.[Captivate_Id], b.[Building_Name] as Building_Name
     ,(s.Server_Name) as Server_Name
     ,ROW_NUMBER() OVER(ORDER BY s.Server_Name DESC) AS Server_Number
      FROM [CAP-CORPCRM02].[CAP_ProductionED].dbo.server s
      join [CAP-CORPCRM02].[CAP_ProductionED].dbo.building b on 
      s.building_id = b.building_id
      WHERE s.Server_Name IS NULL
      UNION ALL
      SELECT b.[Captivate_Id], b.[Building_Name] as Building_Name
      ,(s.Server_Name) as Server_Name
      ,ROW_NUMBER() OVER(ORDER BY s.Server_Name DESC) AS Server_Number 
       FROM [CAP-CORPCRM02].[CAP_ProductionED].dbo.server s
       join [CAP-CORPCRM02].[CAP_ProductionED].dbo.building b on 
        s.building_id = b.building_id

        AND b.Captivate_Id IN 
        (
          'FFF1234'
         )
         )

SELECT Captivate_ID as ID,Building_Name as Building_Name,
(SELECT Server_Name FROM CTE ct WHERE ct.Building_Name=cte.Building_Name 
AND ct.Server_Number=1) Server1,    -----, -- works but order wrong
(SELECT Server_Name FROM CTE ct WHERE ct.Building_Name=cte.Building_Name 
AND ct.Server_Number=2) Server2, -- works but order wrong
(SELECT Server_Name FROM CTE ct WHERE ct.Building_Name=cte.Building_Name 
AND ct.Server_Number=3) Server3

FROM cte 

输出

ID  Building_Name   Server1     Server2     Server3 
FFF1234 some name   servername1 servername2 NULL    
FFF1234 some name   servername1 servername2 NULL

我希望输出是

ID  Building_Name   Server1     Server2     Server3 
FFF1234 some name   servername1 servername2 blank

标签： sqlsql-serversql-server-2008

Answer 1

您对如何ROW_NUMBER()工作有误解。使用两个不同的ROW_NUMBER()命令，您有两个单独的行号序列。您可以通过更改您的 CTE 来纠正您的问题，如下所示：

WITH cte1 (Captivate_Id, Building_Name ,Server_Name)
    AS (
        SELECT b.[Captivate_Id], b.[Building_Name] as Building_Name
            ,'' as Server_Name    -- If you want a blank servername, make it so
        FROM [CAP-CORPCRM02].[CAP_ProductionED].dbo.server s
        join [CAP-CORPCRM02].[CAP_ProductionED].dbo.building b 
            on     s.building_id = b.building_id
        WHERE s.Server_Name IS NULL

        UNION ALL

        SELECT b.[Captivate_Id], b.[Building_Name] as Building_Name
            ,(s.Server_Name) as Server_Name
        FROM [CAP-CORPCRM02].[CAP_ProductionED].dbo.server s
        join [CAP-CORPCRM02].[CAP_ProductionED].dbo.building b 
            on s.building_id = b.building_id
        AND b.Captivate_Id IN (
            'FFF1234'
            )
    ),
cte (Captivate_Id, Building_Name ,Server_Name, Server_Number)
    as (
        SELECT Captivate_ID,
            Building_Name,
            Server_Name,
            ROW_NUMBER() OVER(ORDER BY s.Server_Name DESC) AS Server_Number 
        FROM cte1
    )

SELECT Captivate_ID as ID,Building_Name as Building_Name,
    (SELECT Server_Name FROM CTE ct WHERE ct.Building_Name=cte.Building_Name AND ct.Server_Number=1) Server1,-----, -- works but order wrong
    (SELECT Server_Name FROM CTE ct WHERE ct.Building_Name=cte.Building_Name AND ct.Server_Number=2) Server2, -- works but order wrong
    (SELECT Server_Name FROM CTE ct WHERE ct.Building_Name=cte.Building_Name AND ct.Server_Number=3) Server3
FROM cte

SPOILER - 原始错误代码

WITH cte (Captivate_Id, Building_Name ,Server_Name, Server_Number) as (SELECT Captivate_ID, Building_Name, Server_Name, ROW_NUMBER() OVER(ORDER BY s.Server_Name DESC) AS Server_Number FROM (SELECT b.[Captivate_Id], b.[Building_Name] as Building_Name ,'' as Server_Name -- 如果你想要一个空白的服务器名，那就这样 -- ,ROW_NUMBER() OVER(ORDER BY s.Server_Name DESC) AS Server_Number FROM [CAP-CORPCRM02].[CAP_ProductionED].dbo.server s join [CAP-CORPCRM02].[CAP_ProductionED].dbo.building b on s.building_id = b.building_id WHERE s.Server_Name IS NULL UNION ALL SELECT b.[Captivate_Id], b.[Building_Name] as Building_Name ,(s.Server_Name)作为 Server_Name -- ,ROW_NUMBER() OVER(ORDER BY s.Server_Name DESC) AS Server_Number FROM [CAP-CORPCRM02].[CAP_ProductionED].dbo.server s join [CAP-CORPCRM02].[CAP_ProductionED].dbo.building b on s.building_id = b.building_id AND b.Captivate_Id IN (' FFF1234')))