Provádění zpracování agregace v rámci rekurzivního zpracování pomocí běžných výrazů tabulky

Ukázková data

Použijte tabulky s následujícími hierarchickými daty a tabulky se souhrnnými daty:

Tabulka hierarchie částek

Bodová tabulka

Jak používat běžné výrazy tabulky

Nebudu psát příliš podrobně, protože hlavní téma zde není, ale abych to shrnul jednoduše, běžný výraz tabulky je jako výraz select, který jste předem definovali, a pak můžete použít výraz select, který jste definovali, aniž byste vytvořili více stejného popisu v pozdějším odkazu na tabulku. Může to být obrázek blízký pohledu.

Zde je návod, jak ji používat:

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

výsledek

Reprezentace hierarchických dat

Myslím, že je to pravděpodobně tento vzorec, který dělá běžné výrazy tabulky tak užitečné. Výběrový výraz dříve zavedených dat může být nahrazen jinými věcmi, jako jsou zobrazení a proměnné tabulky, takže toho moc nevyužívá.

Hierarchické reprezentace lze dosáhnout voláním běžnějších výrazů tabulky v běžných výrazech tabulky, jako v následujícím SQL:

Body, na které je třeba dávat pozor při jeho použití, jsou "kombinovat výběr, který je výchozím bodem, a výběr, který spojuje hierarchická data se sjednocením všech" a "sjednotit velikost při kombinování řetězců".

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

Agregace v hierarchickém zpracování běžných tabulkových výrazů (došlo k chybě)

Myslím, že existuje případ, kdy chcete vypočítat agregovanou hodnotu v hierarchickém zpracování společného výrazu tabulky. Zde se pokoušíme vypočítat celkový počet bodů spojených s ID částky.

-- 共通テーブル式の階層処理内で集計を使用する (エラー)
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 když ji spustím, dostanu následující chybu:

Zdá se, že není možné umístit zpracování související se seskupením do rekurzivního společného výrazu tabulky.

Agregace v hierarchickém zpracování běžných výrazů tabulky (normální chování)

Ačkoli není možné popsat proces agregace v rekurzivním společném výrazu tabulky, lze jej provést definováním části zpracování agregace jako společného výrazu tabulky.

Chcete-li napsat více běžných výrazů tabulky, oddělte běžné výrazy tabulky čárkami.

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