Izvajanje procesiranja združevanja znotraj reurzivne obdelave z uporabo skupnih tabelnih izrazov

Vzorčni podatki

Uporabite tabele z naslednjimi hierarhični podatki in tabelami s povzetimi podatki:

Tabela hierarhije zneska

Kazalna tabela

Kako uporabljati pogoste izraze tabele

Ne bom preveč podrobno pisal, ker glavne teme ni tukaj, ampak če povzamem preprosto, je skupni izraz tabele kot izbrani izraz, ki ste ga vnaprej določili in potem lahko uporabite izbirni izraz, ki ste ga definirali, ne da bi ustvarili več enakih opisov v poznejši referenci tabele. Morda je slika blizu pogleda.

Tukaj je, kako ga uporabljati:

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

rezultat

Prikaz hierarhičnih podatkov

Mislim, da je ta vzorec tisti, ki naredi običajne izraze mize tako koristne. Izbrani izraz prej predstavljenih podatkov je mogoče nadomestiti z drugimi stvarmi, kot so pogledi in spremenljivke tabele, zato ne izkorišča veliko.

Hierarhično predstavitev je mogoče doseči s klicem pogostijših izrazov tabele v skupnih izrazih tabele, kot v naslednjem SQL:

Točke, ki jih je treba paziti, ko jih uporabljate, so "združiti izbiro, ki je izhodišče, in izberite, ki povezuje hierarhične podatke z združitvijo vseh" in "poenotiti velikost pri združevanju nizov".

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

Združevanje v hierarhijo obdelave skupnih tabelnih izrazov (prišlo je do napake)

Mislim, da obstaja primer, ko želite izračunati skupno vrednost v hierarhični obdelavi skupnega izraza tabele. Tukaj poskušamo izračunati skupno število točk, povezanih z ID-jem zneska.

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

Ko pa ga zaženem, dobim naslednjo napako:

Zdi se, da obdelave, povezane s združevanjem, ni mogoče dati v ponavljajoči se skupni izraz tabele.

Združevanje v hierarhični obdelavi skupnih izrazov tabele (normalno vedenje)

Čeprav postopka združevanja ni mogoče opisati v ponavljajočem se skupnem izrazu tabele, ga je mogoče izvršiti tako, da se del obdelave združevanja opredeli kot skupni izraz tabele.

Če želite napisati več skupnih izrazov tabele, ločite skupne izraze tabele z vejami.

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