Виконання обробки агрегації всередині рекурсивної обробки з використанням загальних виразів таблиці

Зразок даних

Використовуйте таблиці з такими ієрархічними даними та таблицями з узагальненими даними:

Таблиця ієрархії сум

Точкова таблиця

Використання загальних виразів таблиці

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

Ось як його використовувати:

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

результат

Представлення ієрархічних даних

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

Ієрархічне представлення може бути досягнуто шляхом виклику більш поширених виразів таблиці в загальних виразах таблиці, як у наступному 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]
;

результат