Esecuzione dell'elaborazione di aggregazione all'interno dell'elaborazione ricorsiva mediante espressioni di tabella comuni

Dati di esempio

Utilizzare tabelle con i dati gerarchici seguenti e tabelle con dati riepilogativi:

Tabella della gerarchia degli importi

Tabella dei punti

Come utilizzare espressioni di tabella comuni

Non scriverò troppo in dettaglio perché l'argomento principale non è qui, ma per riassumerlo semplicemente, un'espressione di tabella comune è come un'espressione di selezione che hai definito in anticipo e quindi puoi usare un'espressione di selezione che hai definito senza creare più della stessa descrizione in un riferimento di tabella successivo. Potrebbe trattarsi di un'immagine vicina alla vista.

Ecco come usarlo:

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

risultato

Rappresentazione di dati gerarchici

Penso che sia probabilmente questo modello che rende le espressioni di tabella comuni così utili. L'espressione select dei dati introdotti in precedenza può essere sostituita da altre cose come viste e variabili di tabella, quindi non sfrutta molto.

La rappresentazione gerarchica può essere ottenuta chiamando espressioni di tabella più comuni in espressioni di tabella comuni, come nel seguente SQL:

I punti a cui prestare attenzione quando lo si utilizza sono "combinare la selezione che è il punto di partenza e la selezione che collega i dati gerarchici con l'unione di tutti" e "unificare la dimensione quando si combinano le stringhe".

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

risultato

Aggregazione all'interno della gerarchia elaborazione di espressioni di tabella comuni (errore verificatosi)

Penso che ci sia un caso in cui si desidera calcolare il valore aggregato nell'elaborazione gerarchica dell'espressione di tabella comune. Qui, stiamo cercando di calcolare il numero totale di punti collegati all'ID dell'importo.

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

Ma quando lo eseguo, ottengo il seguente errore:

Sembra che non sia possibile inserire l'elaborazione correlata al raggruppamento in un'espressione di tabella comune ricorsiva.

Aggregazione nell'elaborazione gerarchica di espressioni di tabella comuni (comportamento normale)

Sebbene non sia possibile descrivere il processo di aggregazione nell'espressione di tabella comune ricorsiva, è possibile eseguirlo definendo la parte di elaborazione dell'aggregazione come espressione di tabella comune.

Per scrivere più espressioni di tabella comuni, separare le espressioni di tabella comuni con virgole.

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

risultato