Agregavimo apdorojimo atlikimas rekursinio apdorojimo metu naudojant bendrąsias lentelės išraiškas

Duomenų pavyzdys

Naudokite lenteles su šiais hierarchiniais duomenimis ir lenteles su apibendrintais duomenimis:

Sumų hierarchijos lentelė

Taškų lentelė

Kaip naudoti bendrąsias lentelių išraiškas

Aš nerašysiu per daug išsamiai, nes pagrindinė tema nėra čia, bet apibendrinant ją paprastai, bendra lentelės išraiška yra tarsi pasirinkta išraiška, kurią iš anksto apibrėžėte, ir tada galite naudoti pasirinktą išraišką, kurią nustatėte, nesukurdami to paties aprašo kartotinio vėlesnėje lentelės nuorodoje. Tai gali būti vaizdas, artimas vaizdui.

Štai kaip jį naudoti:

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

rezultatas

Hierarchinių duomenų pateikimas

Manau, kad tai tikriausiai yra šis modelis, dėl kurio bendros lentelės išraiškos yra tokios naudingos. Anksčiau pristatytų duomenų pasirinkta išraiška gali būti pakeista kitais dalykais, pvz., Rodiniais ir lentelės kintamaisiais, todėl ji nepasinaudoja daug.

Hierarchinį atstovavimą galima pasiekti iškviečiant bendresnes lentelių išraiškas bendrosios lentelės išraiškose, kaip ir šiame SQL:

Nurodo, kad jį naudojant reikia "sujungti pasirinkimą, kuris yra pradinis taškas, ir pasirinkimą, kuris jungia hierarchinius duomenis su visais sujungimais" ir "suvienodinti dydį derinant eilutes".

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

rezultatas

Bendrųjų lentelių išraiškų apibendrinimas hierarchijos apdorojime (įvyko klaida)

Manau, kad yra atvejis, kai norite apskaičiuoti apibendrintą vertę hierarchiškai apdorojant bendrosios lentelės išraišką. Čia bandome apskaičiuoti bendrą taškų, susietų su sumos ID, skaičių.

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

Bet kai jį paleidau, gaunu šią klaidą:

Atrodo, kad su grupavimu susijusio apdorojimo neįmanoma įdėti į rekursinę bendrą lentelės išraišką.

Bendrųjų lentelių išraiškų hierarchinio apdorojimo agregavimas (įprastas elgesys)

Nors agregavimo proceso neįmanoma apibūdinti rekursyvioje bendrosios lentelės išraiškoje, jį galima vykdyti apibrėžiant agregavimo apdorojimo dalį kaip bendrą lentelės išraišką.

Norėdami parašyti kelias bendrąsias lentelės išraiškas, atskirkite bendrosios lentelės išraiškas kableliais.

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

rezultatas