Выполнение обработки агрегирования внутри рекурсивной обработки с использованием общих табличных выражений

Пример данных

Используйте таблицы со следующими иерархическими данными и таблицы со сводными данными:

Таблица иерархии сумм

Таблица точек

Использование общих табличных выражений

Я не буду писать слишком подробно, потому что основной темы здесь нет, но, чтобы резюмировать ее просто, общее табличное выражение похоже на выражение select, которое вы определили заранее, а затем вы можете использовать выражение select, которое вы определили, не создавая несколько одинаковых описаний в более поздней ссылке на таблицу. Это может быть изображение, близкое к виду.

Вот как его использовать:

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

результат

Представление иерархических данных

Я думаю, что, вероятно, именно этот шаблон делает общие табличные выражения такими полезными. Выражение select данных, представленное ранее, может быть заменено другими вещами, такими как представления и переменные таблицы, поэтому оно не использует много преимуществ.

Иерархическое представление может быть достигнуто путем вызова более распространенных табличных выражений в общих табличных выражениях, как в следующем SQL:

При его использовании следует соблюдать осторожность: «объединить выделение, которое является начальной точкой, и выделение, которое связывает иерархические данные с объединением всех» и «унифицировать размер при объединении строк».

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

результат

Агрегирование внутри иерархии обработки общих табличных выражений (произошла ошибка)

Я думаю, что есть случай, когда требуется вычислить агрегированное значение в иерархической обработке общего табличного выражения. Здесь мы пытаемся рассчитать общее количество баллов, привязанных к идентификатору суммы.

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

Но когда я запускаю его, я получаю следующую ошибку:

Похоже, что невозможно поместить обработку, связанную с группировкой, в рекурсивное общее табличное выражение.

Агрегирование в иерархической обработке общих табличных выражений (нормальное поведение)

Хотя невозможно описать процесс агрегирования в рекурсивном общем табличном выражении, его можно выполнить, определив часть обработки агрегирования как общее табличное выражение.

Чтобы написать несколько общих табличных выражений, разделяйте общие табличные выражения запятыми.

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

результат