Vykonávanie agregačného spracovania vo vnútri rekurzívneho spracovania pomocou bežných výrazov tabuľky

Vzorové údaje

Použite tabuľky s nasledujúcimi hierarchickými údajmi a tabuľky so súhrnnými údajmi:

Tabuľka hierarchie hodnôt

Bodová tabuľka

Používanie bežných výrazov tabuľky

Nebudem písať príliš podrobne, pretože hlavná téma tu nie je, ale aby som to jednoducho zhrnul, bežný výraz tabuľky je ako vybraný výraz, ktorý ste vopred definovali, a potom môžete použiť vybraný výraz, ktorý ste definovali, bez vytvorenia násobku rovnakého popisu v neskoršom odkaze na tabuľku. Môže to byť obrázok blízky pohľadu.

Tu je postup, ako ho používať:

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

výsledok

Reprezentácia hierarchických údajov

Myslím, že je to pravdepodobne tento vzorec, ktorý robí bežné výrazy tabuľky tak užitočnými. Vybrané vyjadrenie údajov zavedených skôr je možné nahradiť inými vecami, ako sú zobrazenia a premenné tabuľky, takže veľa nevyužíva.

Hierarchické znázornenie možno dosiahnuť volaním bežnejších výrazov tabuľky v bežných výrazoch tabuľky, ako je to v nasledujúcom SQL:

Body, na ktoré si treba dávať pozor pri jeho používaní, sú "kombinovať výber, ktorý je východiskovým bodom, a výber, ktorý spája hierarchické údaje so všetkými spojmi" a "zjednotiť veľkosť pri kombinovaní reťazcov".

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

výsledok

Agregácia v rámci hierarchického spracovania bežných výrazov tabuľky (vyskytla sa chyba)

Myslím si, že existuje prípad, keď chcete vypočítať agregovanú hodnotu v hierarchickom spracovaní spoločného výrazu tabuľky. Tu sa snažíme vypočítať celkový počet bodov spojených s ID sumy.

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

Ale keď ho spustím, dostanem nasledujúcu chybu:

Zdá sa, že nie je možné umiestniť spracovanie súvisiace so zoskupovaním do rekurzívneho spoločného výrazu tabuľky.

Agregácia v hierarchickom spracovaní výrazov spoločnej tabuľky (Normálne správanie)

Hoci nie je možné opísať proces agregácie v rekurzívnom výraze spoločnej tabuľky, môže sa vykonať definovaním časti spracovania agregácie ako spoločného výrazu tabuľky.

Ak chcete napísať viacero bežných výrazov tabuľky, oddeľte bežné výrazy tabuľky čiarkami.

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

výsledok