Wykonywanie przetwarzania agregacji wewnątrz przetwarzania rekurencyjnego przy użyciu typowych wyrażeń tabelarycznych

Przykładowe dane

Użyj tabel z następującymi danymi hierarchicznymi i tabel z podsumowanymi danymi:

Tabela hierarchii kwot

Tabela punktów

Jak używać typowych wyrażeń tabeli

Nie będę pisał zbyt szczegółowo, ponieważ główny temat nie jest tutaj, ale podsumowując go po prostu, wspólne wyrażenie tabeli jest jak wyrażenie select, które zdefiniowałeś z góry, a następnie możesz użyć wyrażenia select, które zdefiniowałeś bez tworzenia wielokrotności tego samego opisu w późniejszym odwołaniu do tabeli. Może to być obraz znajdujący się blisko widoku.

Oto jak z niego korzystać:

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

wynik

Reprezentacja danych hierarchicznych

Myślę, że to prawdopodobnie ten wzorzec sprawia, że typowe wyrażenia tabeli są tak przydatne. Wyrażenie select danych wprowadzonych wcześniej można zastąpić innymi rzeczami, takimi jak widoki i zmienne tabeli, więc nie korzysta ono zbyt wiele.

Reprezentację hierarchiczną można osiągnąć, wywołując bardziej typowe wyrażenia tabeli we wspólnych wyrażeniach tabeli, jak w następującym języku SQL:

Punkty, na które należy uważać podczas korzystania z niego, to "połączenie zaznaczenia, które jest punktem początkowym i zaznaczenia, które łączy dane hierarchiczne z połączeniem wszystkich" oraz "ujednolicenie rozmiaru podczas łączenia ciągów".

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

wynik

Agregacja w ramach przetwarzania hierarchii typowych wyrażeń tabeli (wystąpił błąd)

Myślę, że istnieje przypadek, w którym chcesz obliczyć zagregowaną wartość w hierarchicznym przetwarzaniu wspólnego wyrażenia tabeli. Tutaj staramy się obliczyć całkowitą liczbę punktów powiązanych z identyfikatorem kwoty.

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

Ale kiedy go uruchamiam, pojawia się następujący błąd:

Wydaje się, że nie jest możliwe umieszczenie przetwarzania związanego z grupowaniem w rekurencyjnym wyrażeniu wspólnej tabeli.

Agregacja w hierarchicznym przetwarzaniu wspólnych wyrażeń tabeli (zachowanie normalne)

Chociaż nie jest możliwe opisanie procesu agregacji w rekurencyjnym wyrażeniu wspólnej tabeli, można go wykonać, definiując część przetwarzania agregacji jako wspólne wyrażenie tabeli.

Aby napisać wiele typowych wyrażeń tabelarycznych, należy oddzielić wyrażenia w tabeli przecinkami.

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

wynik