結合CTE-Unino兩個CTE并加入第三個CTE

我正在尋找組合多個 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

我正在尋找一個完整的連接來獲取所有編輯 ID 的編輯 ID、來源、子源、庫和子庫。

我可以將 CTE 放入 #temptables 或視圖中，然后加入，但是我想知道這是否可以在 CTE 中完成，而無需創建和洗掉臨時表和視圖。

如果我聯合 2 個 ctes 并像這樣加入第 3 個，它將拋出必須有相同數量的運算式

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

uj5u.com熱心網友回復：

發生錯誤是因為您的聯合選擇沒有相同數量的列。如果你連接兩個表，你會得到兩個表的列。如果您將 替換為*特定列，您會注意到聯合中的第一個選擇無法訪問Libraryor SubLibraryId，這是因為連接僅介于Cte_originsand之間CTE_EditLibrary。Cte_suborigins被拋在后面，列數比后者少。

要么join在你完成 之后再做union，或者join兩者都用 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
  )
, 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

標籤：sql服务器tsql公共表表达式

上一篇：僅從一個月的最后一天獲取資料，但從整月計算一列的平均值

下一篇：我可以使用什么來代替“設定身份插入”？