Utföra aggregeringsbearbetning i rekursiv bearbetning med vanliga tabelluttryck

Exempel på data

Använd tabeller med följande hierarkiska data och tabeller med sammanfattade data:

Tabell för beloppshierarki

Punkttabell

Så här använder du vanliga tabelluttryck

Jag kommer inte att skriva för mycket detaljerat eftersom huvudämnet inte finns här, men för att sammanfatta det enkelt är ett vanligt tabelluttryck som ett valt uttryck som du har definierat i förväg och sedan kan du använda ett valt uttryck som du har definierat utan att skapa flera av samma beskrivning i en senare tabellreferens. Det kan vara en bild nära vyn.

Så här använder du det:

-- 共通テーブル式で事前に select 内容を定義する
with [cte] as
(
  select *
  from [金額階層テーブル]
  where [Money] >= 5000
)
select * from [cte]
union all
select * from [cte]
;

resultat

Representation av hierarkiska data

Jag tror att det förmodligen är detta mönster som gör vanliga tabelluttryck så användbara. Det valda uttrycket för de data som introducerades tidigare kan ersättas med andra saker som vyer och tabellvariabler, så det utnyttjar inte mycket.

Hierarkisk representation kan uppnås genom att anropa vanligare tabelluttryck i vanliga tabelluttryck, som i följande SQL:

Punkter att vara försiktig med när du använder den är "att kombinera det val som är utgångspunkten och det val som förbinder hierarkiska data med union all" och "att förena storleken när du kombinerar strängar".

-- 共通テーブル式で階層データをつなげる
with [cte] as
(
  select
    [ID]
   ,[Name]
   ,[ParentID]
   ,[Money] as [金額合計]
   ,1 as [レベル]
   ,cast([Name] as nvarchar(4000)) as [階層]
  from [金額階層テーブル]
  where [ParentID] is null

  union all

  select
    [子].[ID]
   ,[子].[Name]
   ,[子].[ParentID]
   ,[親].[金額合計] + [子].[Money] as [金額合計]
   ,[親].[レベル] + 1 as [レベル]
   ,[親].[階層] + N'⇒' + [子].[Name] as [階層]
  from [金額階層テーブル] [子]
  inner join [cte] [親]
    on [子].[ParentID] = [親].[ID]
)
select *
from [cte]
order by [ID]
;

resultat

Aggregering inom hierarkibearbetningen av vanliga tabelluttryck (fel inträffade)

Jag tror att det finns ett fall där du vill beräkna det aggregerade värdet i den hierarkiska bearbetningen av det gemensamma tabelluttrycket. Här försöker vi beräkna det totala antalet poäng kopplade till belopps-ID.

-- 共通テーブル式の階層処理内で集計を使用する (エラー)
with [cte] as
(
  select
    [ID]
   ,[Name]
   ,[ParentID]
   ,[Money] as [金額合計]
   ,(
      select sum([ポイント])
      from [ポイントテーブル]
      where [金額ID] = [ID]
    ) as [ポイント合計]
   ,1 as [レベル]
   ,cast([Name] as nvarchar(4000)) as [階層]
  from [金額階層テーブル]
  where [ParentID] is null

  union all

  select
    [子].[ID]
   ,[子].[Name]
   ,[子].[ParentID]
   ,[親].[金額合計] + [子].[Money] as [金額合計]
   ,[親].[ポイント合計] + (
                            select sum([ポイント])
                            from [ポイントテーブル]
                            where [金額ID] = [子].[ID]
                          ) as [ポイント合計]
   ,[親].[レベル] + 1 as [レベル]
   ,[親].[階層] + N'⇒' + [子].[Name] as [階層]
  from [金額階層テーブル] [子]
  inner join [cte] [親]
    on [子].[ParentID] = [親].[ID]
)
select *
from [cte]
order by [ID]
;

Men när jag kör det får jag följande fel:

Det verkar som om det inte är möjligt att placera grupperingsrelaterad bearbetning i ett rekursivt gemensamt tabelluttryck.

Aggregering i hierarkisk bearbetning av vanliga tabelluttryck (normalt beteende)

Även om det inte går att beskriva aggregeringsprocessen i det rekursiva gemensamma tabelluttrycket kan den köras genom att definiera aggregeringsbearbetningsdelen som ett gemensamt tabelluttryck.

Om du vill skriva flera vanliga tabelluttryck avgränsar du de vanliga tabelluttrycken med kommatecken.

-- 共通テーブル式の階層処理内で集計を使用する (正常)
with [ポイント合計CTE] as
(
  select
    [金額ID]
   ,sum([ポイント]) as [ポイント合計]
  from [ポイントテーブル]
  group by [金額ID]
)
,[cte] as
(
  select
    [ID]
   ,[Name]
   ,[ParentID]
   ,[Money] as [金額合計]
   ,(
      select [ポイント合計]
      from [ポイント合計CTE]
      where [金額ID] = [ID]
    ) as [ポイント合計]
   ,1 as [レベル]
   ,cast([Name] as nvarchar(4000)) as [階層]
  from [金額階層テーブル]
  where [ParentID] is null

  union all

  select
    [子].[ID]
   ,[子].[Name]
   ,[子].[ParentID]
   ,[親].[金額合計] + [子].[Money] as [金額合計]
   ,[親].[ポイント合計] + (
                            select [ポイント合計]
                            from [ポイント合計CTE]
                            where [金額ID] = [子].[ID]
                          ) as [ポイント合計]
   ,[親].[レベル] + 1 as [レベル]
   ,[親].[階層] + N'⇒' + [子].[Name] as [階層]
  from [金額階層テーブル] [子]
  inner join [cte] [親]
    on [子].[ParentID] = [親].[ID]
)
select *
from [cte]
order by [ID]
;

resultat