Udførelse af aggregeringsbehandling i rekursiv behandling ved hjælp af almindelige tabeludtryk

Eksempel på data

Brug tabeller med følgende hierarkiske data og tabeller med opsummerede data:

Tabel over beløbshierarki

Punkttabel

Sådan bruger du almindelige tabeludtryk

Jeg vil ikke skrive for meget detaljeret, fordi hovedemnet ikke er her, men for at opsummere det simpelthen er et fælles tabeludtryk som et udvalgt udtryk, som du har defineret på forhånd, og så kan du bruge et valgt udtryk, som du har defineret uden at oprette flere af den samme beskrivelse i en senere tabelreference. Det kan være et billede tæt på udsigten.

Sådan bruges det:

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

resultat

Repræsentation af hierarkiske data

Jeg tror, det sandsynligvis er dette mønster, der gør almindelige tabeludtryk så nyttige. Det valgte udtryk for de data, der blev introduceret tidligere, kan erstattes af andre ting såsom visninger og tabelvariabler, så det ikke udnytter meget.

Hierarkisk repræsentation kan opnås ved at kalde mere almindelige tabeludtryk i almindelige tabeludtryk som i følgende SQL:

Punkter, der skal være forsigtige med, når du bruger det, er "at kombinere det valg, der er udgangspunktet, og det valg, der forbinder de hierarkiske data med union all" og "at forene størrelsen, når du kombinerer strenge".

-- 共通テーブル式で階層データをつなげる
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 i hierarkiet behandling af almindelige tabeludtryk (der opstod fejl)

Jeg tror, at der er et tilfælde, hvor du vil beregne den aggregerede værdi i den hierarkiske behandling af det fælles tabeludtryk. Her forsøger vi at beregne det samlede antal point, der er knyttet til beløbs-id'et.

-- 共通テーブル式の階層処理内で集計を使用する (エラー)
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 jeg kører det, får jeg følgende fejl:

Det ser ud til, at det ikke er muligt at placere grupperingsrelateret behandling i et rekursivt fælles tabeludtryk.

Aggregering i den hierarkiske behandling af almindelige tabeludtryk (normal funktionsmåde)

Selvom det ikke er muligt at beskrive sammenlægningsprocessen i det rekursive fælles tabeludtryk, kan den udføres ved at definere sammenlægningsbehandlingsdelen som et fælles tabeludtryk.

Hvis du vil skrive flere almindelige tabeludtryk, skal du adskille de almindelige tabeludtryk med kommaer.

-- 共通テーブル式の階層処理内で集計を使用する (正常)
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