Ausführen der Aggregationsverarbeitung innerhalb der rekursiven Verarbeitung unter Verwendung allgemeiner Tabellenausdrücke

Beispieldaten

Verwenden Sie Tabellen mit den folgenden hierarchischen Daten und Tabellen mit zusammengefassten Daten:

Betragshierarchietabelle

Punktetabelle

Verwenden allgemeiner Tabellenausdrücke

Ich werde nicht zu detailliert schreiben, weil das Hauptthema nicht hier ist, aber um es einfach zusammenzufassen, ist ein allgemeiner Tabellenausdruck wie ein Auswahlausdruck, den Sie im Voraus definiert haben, und dann können Sie einen Auswahlausdruck verwenden, den Sie definiert haben, ohne ein Vielfaches derselben Beschreibung in einer späteren Tabellenreferenz zu erstellen. Es kann sich um ein Bild in der Nähe der Ansicht handeln.

So verwenden Sie es:

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

Ergebnis

Darstellung hierarchischer Daten

Ich denke, es ist wahrscheinlich dieses Muster, das gängige Tabellenausdrücke so nützlich macht. Der Auswahlausdruck der zuvor eingeführten Daten kann durch andere Dinge wie Ansichten und Tabellenvariablen ersetzt werden, sodass er nicht viel ausnutzt.

Die hierarchische Darstellung kann erreicht werden, indem allgemeinere Tabellenausdrücke in allgemeinen Tabellenausdrücken wie im folgenden SQL aufgerufen werden:

Punkte, auf die Sie bei der Verwendung achten sollten, sind "das Kombinieren der Auswahl, die der Ausgangspunkt ist, und der Auswahl, die die hierarchischen Daten mit der Vereinigung aller verbindet" und "um die Größe beim Kombinieren von Zeichenfolgen zu vereinheitlichen".

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

Ergebnis

Aggregation innerhalb der Hierarchieverarbeitung gängiger Tabellenausdrücke (Fehler aufgetreten)

Ich denke, dass es einen Fall gibt, in dem Sie den aggregierten Wert in der hierarchischen Verarbeitung des allgemeinen Tabellenausdrucks berechnen möchten. Hier versuchen wir, die Gesamtzahl der Punkte zu berechnen, die mit der Betrags-ID verknüpft sind.

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

Aber wenn ich es ausführe, erhalte ich den folgenden Fehler:

Es scheint, dass es nicht möglich ist, gruppierungsbezogene Verarbeitung in einen rekursiven allgemeinen Tabellenausdruck zu setzen.

Aggregation in der hierarchischen Verarbeitung gängiger Tabellenausdrücke (Normalverhalten)

Obwohl es nicht möglich ist, den Aggregationsprozess im rekursiven allgemeinen Tabellenausdruck zu beschreiben, kann er ausgeführt werden, indem der Aggregationsverarbeitungsteil als allgemeiner Tabellenausdruck definiert wird.

Um mehrere allgemeine Tabellenausdrücke zu schreiben, trennen Sie die allgemeinen Tabellenausdrücke durch Kommas.

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

Ergebnis