Efectuarea procesării agregării în interiorul procesării recursive utilizând expresii de tabel comune

Date eșantion

Utilizați tabele cu următoarele date ierarhice și tabele cu date rezumate:

Tabel ierarhie cantitate

Tabel punct

Cum se utilizează expresiile de tabel comune

Nu voi scrie în prea multe detalii, deoarece subiectul principal nu este aici, ci pentru a-l rezuma pur și simplu, o expresie de tabel comună este ca o expresie selectă pe care ați definit-o în prealabil și apoi puteți utiliza o expresie selectă pe care ați definit-o fără a crea mai multe din aceeași descriere într-o referință de tabel ulterioară. Poate fi o imagine apropiată de vizualizare.

Iată cum să-l utilizați:

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

rezultat

Reprezentarea datelor ierarhice

Cred că este, probabil, acest model care face expresii comune tabel atât de util. Expresia selectă a datelor introduse mai devreme poate fi înlocuită cu alte lucruri, cum ar fi vizualizările și variabilele de tabel, astfel încât să nu profite prea mult.

Reprezentarea ierarhică poate fi realizată apelând expresii de tabel mai comune în expresii de tabel comune, ca în următorul SQL:

Punctele de care trebuie să aveți grijă atunci când îl utilizați sunt "pentru a combina selectați care este punctul de plecare și selectați care conectează datele ierarhice cu unirea tuturor" și "pentru a unifica dimensiunea atunci când combinați șiruri".

-- 共通テーブル式で階層データをつなげる
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]
;

rezultat

Agregarea în ierarhie procesarea expresiilor de tabel comune (a apărut eroarea)

Cred că există un caz în care doriți să calculați valoarea agregată în prelucrarea ierarhică a expresiei tabelului comun. Aici, încercăm să calculăm numărul total de puncte legate de ID-ul sumei.

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

Dar când îl rulez, primesc următoarea eroare:

Se pare că nu este posibil să se pună procesarea legată de grupare într-o expresie de tabel comun recursivă.

Agregarea în procesarea ierarhică a expresiilor de tabel comune (Comportament normal)

Deși nu este posibil să se descrie procesul de agregare în expresia tabelului comun recursiv, acesta poate fi executat prin definirea părții de procesare a agregării ca expresie de tabel comună.

Pentru a scrie mai multe expresii de tabel comune, separați expresiile de tabel comune cu virgule.

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

rezultat