Извършване на обработка на сумиране вътре в рекурсивна обработка с помощта на общи изрази на таблица

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

Използвайте таблици със следните йерархични данни и таблици с обобщени данни:

Таблица на йерархията на сумите

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

Как да използвате често срещани изрази на таблица

Няма да пиша твърде много подробно, защото основната тема не е тук, а за да я обобщим просто, общ израз на таблица е като избран израз, който сте определили предварително и след това можете да използвате избран израз, който сте определили, без да създавате кратно на същото описание в по-късна препратка към таблица. Може да е изображение близо до изгледа.

Ето как да го използвате:

-- 共通テーブル式で事前に 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]
;

резултат