Realització de processament d'agregació dins del processament recursiu utilitzant expressions de taula comunes

Dades d'exemple

Utilitzeu taules amb les dades jeràrquiques i taules següents amb dades resumides:

Taula de jerarquia d'imports

Taula de punts

Com utilitzar expressions de taula habituals

No escriuré amb massa detall perquè el tema principal no és aquí, sinó per resumir-lo simplement, una expressió de taula comuna és com una expressió seleccionada que heu definit amb antelació i, a continuació, podeu utilitzar una expressió seleccionada que heu definit sense crear múltiples de la mateixa descripció en una referència de taula posterior. Pot ser una imatge propera a la vista.

A continuació s'explica com utilitzar-lo:

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

resultat

Representació de dades jeràrquiques

Crec que és probablement aquest patró el que fa que les expressions de taula comunes siguin tan útils. L'expressió selecta de les dades introduïdes anteriorment es pot substituir per altres coses com vistes i variables de taula, de manera que no aprofita molt.

La representació jeràrquica es pot aconseguir cridant expressions de taula més comunes en expressions de taula comunes, com en el següent SQL:

Els punts a tenir en compte a l'hora d'utilitzar-lo són "combinar la selecció que és el punt de partida i la selecció que connecta les dades jeràrquiques amb la unió de tots" i "unificar la mida en combinar cadenes".

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

resultat

Agregació dins del processament de jerarquies d'expressions de taula comunes (s'ha produït un error)

Crec que hi ha un cas en què voleu calcular el valor agregat en el processament jeràrquic de l'expressió de la taula comuna. Aquí, estem intentant calcular el nombre total de punts vinculats a l'identificador de l'import.

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

Però quan l'executo, retenc el següent error:

Sembla que no és possible posar el processament relacionat amb l'agrupació en una expressió recursiva de taula comuna.

Agregació en el processament jeràrquic d'expressions de taula comunes (comportament normal)

Tot i que no és possible descriure el procés d'agregació en l'expressió recursiva de la taula comuna, es pot executar definint la part de processament d'agregació com una expressió de taula comuna.

Per escriure diverses expressions de taula comunes, separeu les expressions de taula comunes amb comes.

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

resultat