Aggregatieverwerking uitvoeren binnen recursieve verwerking met behulp van algemene tabelexpressies

Voorbeeldgegevens

Gebruik tabellen met de volgende hiërarchische gegevens en tabellen met samengevatte gegevens:

Tabel met bedraghiërarchie

Puntentabel

Algemene tabelexpressies gebruiken

Ik zal niet te veel in detail schrijven omdat het hoofdonderwerp hier niet is, maar om het eenvoudig samen te vatten, een gemeenschappelijke tabelexpressie is als een selectie-expressie die u van tevoren hebt gedefinieerd en dan kunt u een selectie-expressie gebruiken die u hebt gedefinieerd zonder een veelvoud van dezelfde beschrijving te maken in een latere tabelverwijzing. Het kan een afbeelding zijn die dicht bij de weergave ligt.

Hier leest u hoe u het kunt gebruiken:

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

resultaat

Weergave van hiërarchische gegevens

Ik denk dat het waarschijnlijk dit patroon is dat veelvoorkomende tabeluitdrukkingen zo nuttig maakt. De selecte expressie van de eerder geïntroduceerde gegevens kan worden vervangen door andere dingen zoals weergaven en tabelvariabelen, dus het maakt niet veel gebruik.

Hiërarchische representatie kan worden bereikt door meer algemene tabelexpressies aan te roepen in algemene tabelexpressies, zoals in de volgende SQL:

Punten om voorzichtig mee te zijn bij het gebruik ervan zijn "het combineren van de selectie die het startpunt is en de selectie die de hiërarchische gegevens verbindt met het samenvoegen van alles" en "om de grootte te verenigen bij het combineren van tekenreeksen".

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

resultaat

Aggregatie binnen de hiërarchieverwerking van algemene tabelexpressies (fout opgetreden)

Ik denk dat er een geval is waarin je de geaggregeerde waarde wilt berekenen in de hiërarchische verwerking van de gemeenschappelijke tabelexpressie. Hier proberen we het totale aantal punten te berekenen dat is gekoppeld aan de hoeveelheids-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]
;

Maar als ik het uitvoer, krijg ik de volgende fout:

Het lijkt erop dat het niet mogelijk is om groeperingsgerelateerde verwerking in een recursieve gemeenschappelijke tabelexpressie te plaatsen.

Aggregatie in de hiërarchische verwerking van algemene tabelexpressies (normaal gedrag)

Hoewel het niet mogelijk is om het aggregatieproces in de recursieve gemeenschappelijke tabelexpressie te beschrijven, kan het worden uitgevoerd door het aggregatieverwerkingsgedeelte te definiëren als een gemeenschappelijke tabelexpressie.

Als u meerdere algemene tabelexpressies wilt schrijven, scheidt u de algemene tabelexpressies met komma's.

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

resultaat