Realizando processamento de agregação dentro do processamento recursivo usando expressões comuns de tabela

Dados amostrais

Use tabelas com os seguintes dados hierárquicos e tabelas com dados resumidos:

Tabela de hierarquia de quantidade

Tabela de pontos

Como usar expressões comuns de mesa

Não escreverei com muitos detalhes porque o tópico principal não está aqui, mas para resumi-lo simplesmente, uma expressão de tabela comum é como uma expressão de tabela selecionada que você definiu com antecedência e, em seguida, você pode usar uma expressão selecionada que você definiu sem criar vários da mesma descrição em uma referência de tabela posterior. Pode ser uma imagem próxima à vista.

Veja como usá-lo:

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

resultado

Representação de dados hierárquicos

Eu acho que é provavelmente este padrão que torna as expressões comuns da mesa tão úteis. A expressão selecionada dos dados introduzidos anteriormente pode ser substituída por outras coisas, como visualizações e variáveis de tabela, por isso não aproveita muito.

A representação hierárquica pode ser alcançada chamando expressões de tabela mais comuns em expressões comuns de tabela, como no seguinte SQL:

Os pontos a serem cuidadosos ao usá-lo são "combinar o seleto que é o ponto de partida e o seleto que conecta os dados hierárquicos com todos os sindicatos" e "unificar o tamanho ao combinar strings".

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

resultado

Agregação dentro do processamento hierárquico de expressões comuns de tabela (ocorreu erro)

Eu acho que há um caso em que você quer calcular o valor agregado no processamento hierárquico da expressão de tabela comum. Aqui, estamos tentando calcular o número total de pontos ligados ao ID de valor.

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

Mas quando eu executá-lo, eu recebo o seguinte erro:

Parece que não é possível colocar o processamento relacionado ao agrupamento em uma expressão de tabela comum recursiva.

Agregação no Processamento Hierárquico de Expressões comuns de tabela (Comportamento Normal)

Embora não seja possível descrever o processo de agregação na expressão de tabela comum recursiva, ele pode ser executado definindo a parte de processamento de agregação como uma expressão de tabela comum.

Para escrever várias expressões comuns de tabela, separe as expressões comuns da tabela com as írgulas.

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

resultado