Liitmise töötlemine rekursiivses töötlemises ühiste tabeliavaldiste abil

Näidisandmed

Tabelite kasutamine järgmiste hierarhiliste andmetega ja tabelid summeeritud andmetega.

Tabel Summa hierarhia

Punkttabel

Levinud tabeliavaldiste kasutamine

Ma ei kirjuta liiga üksikasjalikult, sest põhiteemat ei ole siin, kuid selle lihtsalt kokkuvõtmiseks on tavaline tabeliavaldis nagu eelnevalt määratletud valitud avaldis ja siis saate kasutada valitud avaldist, mille olete määratlenud, ilma et loodaks hilisemas tabeliviites sama kirjeldust. See võib olla pilt vaate lähedal.

Selle kasutamiseks tehke järgmist.

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

tulemus

Hierarhiliste andmete esitamine

Ma arvan, et see on ilmselt see muster, mis muudab tavalised lauaavaldised nii kasulikuks. Varem esitatud andmete valitud avaldist saab asendada muude asjadega, nagu vaated ja tabelimuutujad, nii et see ei kasuta palju ära.

Hierarhiline esitus on saavutatav, kutsudes tavalisemaid tabeliavaldisi tavalisemateks tabeliavaldisteks tavalistes tabeliavaldistes, nagu ka järgmises SQL-is.

Punktid, mida tuleb selle kasutamisel ettevaatlik olla, on "ühendada valik, mis on lähtepunkt, ja valik, mis ühendab hierarhiliste andmete liidu kõigiga" ja "suuruse ühendamine stringide ühendamisel".

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

tulemus

Liitmine levinud tabeliavaldiste hierarhia töötlemisel (ilmnes tõrge)

Ma arvan, et on juhtum, kus soovite arvutada koondväärtuse ühise tabeliavaldise hierarhilises töötlemises. Siin püüame arvutada summa ID-ga seotud punktide koguarvu.

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

Aga kui ma seda käivitan, kuvatakse järgmine viga:

Tundub, et rühmitamisega seotud töötlemist ei ole võimalik paigutada rekursiivsesse ühisesse tabeliavaldisse.

Liitmine levinud tabeliavaldiste hierarhilises töötlemises (normaalne käitumine)

Kuigi agregatsiooniprotsessi ei ole võimalik kirjeldada rekursiivses ühises tabeliavaldises, saab seda käivitada, määratledes liitmise töötlemise osa ühise tabeliavaldisena.

Mitme levinud tabeliavaldise kirjutamiseks eraldage ühised tabeliavaldised komadega.

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

tulemus