Realización del procesamiento de agregación dentro del procesamiento recursivo mediante expresiones de tabla comunes

Datos de muestra

Utilice tablas con los siguientes datos jerárquicos y tablas con datos resumidos:

Tabla de jerarquía de importes

Tabla de puntos

Cómo utilizar expresiones de tabla comunes

No escribiré con demasiado detalle porque el tema principal no está aquí, pero para resumirlo simplemente, una expresión de tabla común es como una expresión de selección que ha definido de antemano y luego puede usar una expresión de selección que haya definido sin crear múltiplos de la misma descripción en una referencia de tabla posterior. Puede ser una imagen cercana a la vista.

A continuación, le indicamos cómo usarlo:

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

resultado

Representación de datos jerárquicos

Creo que es probablemente este patrón el que hace que las expresiones de tabla comunes sean tan útiles. La expresión selecta de los datos introducidos anteriormente se puede sustituir por otras cosas como vistas y variables de tabla, por lo que no aprovecha mucho.

La representación jerárquica se puede lograr llamando a expresiones de tabla más comunes en expresiones de tabla comunes, como en el siguiente SQL:

Los puntos a tener cuidado al usarlo son "combinar el select que es el punto de partida y el select que conecta los datos jerárquicos con la unión de todos" y "unificar el tamaño al combinar cadenas".

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

Agregación dentro del procesamiento jerárquico de expresiones de tabla comunes (error)

Creo que hay un caso en el que se desea calcular el valor agregado en el procesamiento jerárquico de la expresión de tabla común. Aquí, estamos tratando de calcular el número total de puntos vinculados a la cantidad ID.

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

Pero cuando lo ejecuto, obtengo el siguiente error:

Parece que no es posible poner el procesamiento relacionado con la agrupación en una expresión de tabla común recursiva.

Agregación en el procesamiento jerárquico de expresiones de tabla comunes (comportamiento normal)

Aunque no es posible describir el proceso de agregación en la expresión de tabla común recursiva, se puede ejecutar definiendo la parte de procesamiento de agregación como una expresión de tabla común.

Para escribir varias expresiones de tabla comunes, separe las expresiones de tabla comunes con comas.

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