结合CTEs - Unino 2 CTE与第3 CTE

Question

我希望结合多个CTE来获得结果。

这是我的第一次CTE

    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的结果

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

我正在寻找一个完整的连接，以获得所有编辑ids的editid、源文件、子源、库和子库。

我可以将CTE放入#temptables或视图中，然后加入，但是我想知道这是否可以在CTE中完成，而不需要创建和删除临时表和视图。

如果我合并2 ctes，并像这样与第3 ctes连接，则抛出的表达式必须数量相等。

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

Answer 1

发生此错误是因为您的联合选择没有相同数量的列。如果连接两个表，则会得到两个表的列。如果要用特定的列替换*，就会注意到联合中的第一个select不能访问Library或SubLibraryId，这是因为连接只在Cte_origins和CTE_EditLibrary之间。Cte_suborigins被抛在后面，列数比后者少。

要么在完成join之后执行union，要么在两个CTEs中同时执行join

 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
  )
, 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 (
    select * from Cte_suborigins A
    union all 
    select * from Cte_origins B
  ) d
  full join CTE_EditLibrary C 
    on d.EditId = c.EditId