SQL两次INNER JOIN同表取不同RelationshipType值的实现与报错处理

两个报错的根因 #

• 第一个Msg 319报错：SQL Server要求CTE语句的前一条语句必须以分号结尾，最稳妥的写法是直接在WITH关键字前加一个分号，避免前面语句结尾漏写分号触发语法错误。
• 第二个Msg 207报错：你在CTE中查询BMEt.Name时没有显式指定列别名，部分SQL Server版本对未显式命名的派生列识别存在问题，只要给BMEt.Name加AS RelValue的别名，PIVOT中对应使用max(RelValue)即可。

方案1：修复后的PIVOT写法 #

DECLARE @OSBuild AS VARCHAR(100)='10.0.19042';

-- 前面加;避免前序语句无分号报错
;WITH a AS (
    SELECT
      VRS.[ResourceID] AS [ResourceID]
      , VRS.[Name0] AS [Computername]
      , VRS.[Resource_Domain_OR_Workgr0] AS [Domain]
      , BIOS.SerialNumber0 AS [Serialnumber]
      , CASE RT.RelationshipTypeName
          WHEN 'Cireson.AssetManagement.HardwareAssetHasOrganization'
          THEN 'Organization'
          WHEN 'Cireson.AssetManagement.HardwareAssetHasLocation'
          THEN 'Location'
        END AS RelTypeName
      , BMEt.Name AS RelValue -- 显式给列取别名，避免PIVOT识别不到
    FROM [SERVER02].[dbo].[v_R_System] as VRS
      INNER JOIN [SERVER02].[dbo].[v_GS_OPERATING_SYSTEM] AS vGSOS
        ON vGSOS.[ResourceID] = VRS.[ResourceID] 
      INNER JOIN [SERVER02].[dbo].v_GS_PC_BIOS AS BIOS
        ON BIOS.[ResourceID] = VRS.[ResourceID]
      INNER JOIN [SERVER01].[ServiceManager].[dbo].[MT_Cireson$AssetManagement$HardwareAsset] AS HWA
        ON BIOS.SerialNumber0 = HWA.SerialNumber_C8CF2E89_7A83_1C26_0AD0_887DF9140D5A COLLATE SQL_Latin1_General_CP1_CI_AS
      INNER JOIN [SERVER01].[ServiceManager].[dbo].relationship AS rel
        ON rel.SourceEntityId = HWA.BaseManagedEntityId
      INNER JOIN [SERVER01].[ServiceManager].[dbo].[RelationshipType] AS RT
        ON rel.RelationshipTypeId = RT.RelationshipTypeId 
          AND RT.RelationshipTypeName IN ('Cireson.AssetManagement.HardwareAssetHasOrganization','Cireson.AssetManagement.HardwareAssetHasLocation')
      INNER JOIN [SERVER01].[ServiceManager].[dbo].[BaseManagedEntity] AS BMEt
        ON rel.TargetEntityId = BMEt.BaseManagedEntityId
    WHERE VRS.Operating_System_Name_and0 NOT LIKE '%Server%'
      AND vGSOS.[Version0] = @OSBuild
)
SELECT 
  [ResourceID],
  [Computername],
  [Domain],
  [Serialnumber],
  [Location],
  [Organization]
FROM a
PIVOT (
  MAX(RelValue) FOR RelTypeName IN (
    [Location],
    [Organization]
  )
) AS q
ORDER BY [Serialnumber] ASC

方案2：更简单的两次LEFT JOIN写法（推荐） #

你之前改别名没有返回数据，是因为用了INNER JOIN，要求资产必须同时存在位置和组织两个关联关系才会返回结果，改为LEFT JOIN即可兼容只有单个属性的资产，逻辑比PIVOT更直观易懂：

DECLARE @OSBuild AS VARCHAR(100)='10.0.19042'

SELECT 
  VRS.[ResourceID] AS 'ResourceID'
  ,VRS.[Name0] AS 'Computername'
  ,VRS.[Resource_Domain_OR_Workgr0] AS 'Domain'
  ,BIOS.SerialNumber0 AS 'Serialnumber'
  ,BMEt_Location.Name AS 'Location' -- 位置关联取的Name
  ,BMEt_Org.Name AS 'Organization' -- 组织关联取的Name
FROM [SERVER02].[dbo].[v_R_System] as VRS
INNER JOIN [SERVER02].[dbo].[v_GS_OPERATING_SYSTEM] AS vGSOS on vGSOS.[ResourceID] = VRS.[ResourceID] 
INNER JOIN [SERVER02].[dbo].v_GS_PC_BIOS AS BIOS ON BIOS.[ResourceID] = VRS.[ResourceID]
INNER JOIN [SERVER01].[ServiceManager].[dbo].[MT_Cireson$AssetManagement$HardwareAsset] AS HWA ON BIOS.SerialNumber0 = HWA.SerialNumber_C8CF2E89_7A83_1C26_0AD0_887DF9140D5A COLLATE SQL_Latin1_General_CP1_CI_AS
INNER JOIN [SERVER01].[ServiceManager].[dbo].relationship AS rel ON rel.SourceEntityId = HWA.BaseManagedEntityId

-- 单独关联位置类型的关系
LEFT JOIN [SERVER01].[ServiceManager].[dbo].[RelationshipType] AS RT_Location 
  ON rel.RelationshipTypeId = RT_Location.RelationshipTypeId 
  AND RT_Location.RelationshipTypeName = 'Cireson.AssetManagement.HardwareAssetHasLocation'
LEFT JOIN [SERVER01].[ServiceManager].[dbo].[BaseManagedEntity] AS BMEt_Location 
  ON rel.TargetEntityId = BMEt_Location.BaseManagedEntityId 
  AND RT_Location.RelationshipTypeId IS NOT NULL -- 只匹配位置关系对应的Target

-- 单独关联组织类型的关系
LEFT JOIN [SERVER01].[ServiceManager].[dbo].[RelationshipType] AS RT_Org 
  ON rel.RelationshipTypeId = RT_Org.RelationshipTypeId 
  AND RT_Org.RelationshipTypeName = 'Cireson.AssetManagement.HardwareAssetHasOrganization'
LEFT JOIN [SERVER01].[ServiceManager].[dbo].[BaseManagedEntity] AS BMEt_Org 
  ON rel.TargetEntityId = BMEt_Org.BaseManagedEntityId 
  AND RT_Org.RelationshipTypeId IS NOT NULL -- 只匹配组织关系对应的Target

WHERE VRS.Operating_System_Name_and0 NOT LIKE '%Server%' 
AND vGSOS.[Version0] = @OSBuild 
ORDER BY BIOS.SerialNumber0 ASC

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