如何在CTE内合并两个CTE并与第三个CTE关联（无需临时表）

我需要合并三个CTE（CTE_EditLibrary、Cte_origins、Cte_suborigins）来获取目标结果：先将Cte_origins与Cte_suborigins执行UNION ALL，再和CTE_EditLibrary做全连接，得到所有editid对应的editid、origins、suborigins、library和sublibrary信息。想确认是否仅通过CTE就能实现该逻辑，无需创建临时表或视图；当前尝试的写法因表达式数量不匹配报错。

现有CTE代码 #

第一个CTE：CTE_EditLibrary #

with CTE_EditLibrary
as
(
  select A.[EditLibraryId] as 'Library'
          ,B.[EditLibraryId] as 'SubLibraryId'
      ,B.[EditId]
      from [EnumEditLibraries] as A 
      full join [EnumEditLibraries] as B 
      on A.editid = B.editid
      where A.EditLibraryId in (select editlibraryid from [EditLibraries] where level = 1)
      and  B.EditLibraryId in (select editlibraryid from [EditLibraries] where level = 2)
     order by A.editid
)
select * from CTE_EditLibrary

需关联的两个CTE：Cte_origins、Cte_suborigins #

with Cte_origins
as
(
SELECT A.editid, A.EditOriginId as 'Origins', B.DisplayName as 'Origin Name',  Null as 'SubOrigins', null as 'SubOrigin Name'
  FROM [EnumEditOrigins] A
    inner join [EditOrigins] B
  on A.EditOriginId = B.EditOriginId
  where A.EditOriginId in (select EditOriginId from [EditOrigins] where level = 1)
  and  editid not in (some values)
  --order by EditId--460, 446
)
, Cte_suborigins
as
(
  SELECT A.editid, B.ParentEditOriginId as 'Origins', Src.DisplayName as 'Origin Name', A.EditOriginId as 'SubOrigins',  B.DisplayName as 'SubOrigin Name'
  FROM [EnumEditOrigins] A
  inner join [EditOrigins] B
  on A.EditOriginId = B.EditOriginId
  inner join (select EditOriginId, DisplayName from [EditOrigins] where level = 1) as Src
  on B.ParentEditOriginId = Src.EditOriginId
  where A.EditOriginId in (select EditOriginId from [EditOrigins] where level = 2)
  and  editid not in (some values)--2562, 2542
 --order by editid
)
select * from Cte_suborigins A
union all 
select * from Cte_origins B

报错的尝试写法 #

With Cte_......
(
.
.
)
 select * from Cte_suborigins A
  union all 
  select * from Cte_origins B
  full join CTE_EditLibrary C 
  on A.EditId = B.EditId

解决方案：用CTE嵌套实现 #

完全可以仅通过CTE实现，无需临时表或视图。把Cte_origins和Cte_suborigins的UNION ALL结果封装成一个新的CTE，再与CTE_EditLibrary做全连接即可：

with CTE_EditLibrary
as
(
  select A.[EditLibraryId] as 'Library'
          ,B.[EditLibraryId] as 'SubLibraryId'
      ,B.[EditId]
      from [EnumEditLibraries] as A 
      full join [EnumEditLibraries] as B 
      on A.editid = B.editid
      where A.EditLibraryId in (select editlibraryid from [EditLibraries] where level = 1)
      and  B.EditLibraryId in (select editlibraryid from [EditLibraries] where level = 2)
),
Cte_origins
as
(
SELECT A.editid, A.EditOriginId as 'Origins', B.DisplayName as 'Origin Name',  Null as 'SubOrigins', null as 'SubOrigin Name'
  FROM [EnumEditOrigins] A
    inner join [EditOrigins] B
  on A.EditOriginId = B.EditOriginId
  where A.EditOriginId in (select EditOriginId from [EditOrigins] where level = 1)
  and  editid not in (some values)
),
Cte_suborigins
as
(
  SELECT A.editid, B.ParentEditOriginId as 'Origins', Src.DisplayName as 'Origin Name', A.EditOriginId as 'SubOrigins',  B.DisplayName as 'SubOrigin Name'
  FROM [EnumEditOrigins] A
  inner join [EditOrigins] B
  on A.EditOriginId = B.EditOriginId
  inner join (select EditOriginId, DisplayName from [EditOrigins] where level = 1) as Src
  on B.ParentEditOriginId = Src.EditOriginId
  where A.EditOriginId in (select EditOriginId from [EditOrigins] where level = 2)
  and  editid not in (some values)
),
Cte_CombinedOrigins
as
(
  select * from Cte_suborigins
  union all 
  select * from Cte_origins
)
select 
  CO.editid,
  CO.Origins,
  CO.[Origin Name],
  CO.SubOrigins,
  CO.[SubOrigin Name],
  EL.Library,
  EL.SubLibraryId as SubLibrary
from Cte_CombinedOrigins CO
full join CTE_EditLibrary EL
  on CO.editid = EL.EditId

错误原因说明 #

之前的写法错误在于把UNION ALL和JOIN的逻辑顺序搞反了，直接在UNION ALL之后接JOIN会导致SQL解析器无法正确识别关联关系，进而出现表达式数量不匹配的报错。通过将UNION ALL结果封装为独立CTE，再与目标CTE做全连接，就能清晰实现所需逻辑。

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