Összesítésfeldolgozás végrehajtása rekurzív feldolgozáson belül gyakori táblakifejezések használatával

Mintaadatok

Használjon táblázatokat a következő hierarchikus adatokat és összesített adatokat tartalmazó táblákat:

Összeghierarchia tábla

Ponttábla

Gyakori táblakifejezések használata

Nem fogok túl sok részletet írni, mert a fő téma nincs itt, de egyszerűen összefoglalva, a közös táblázatkifejezés olyan, mint egy előre definiált választókifejezés, majd használhat egy kiválasztott kifejezést, amelyet meghatározott anélkül, hogy ugyanazt a leírást létrehozná egy későbbi táblázathivatkozásban. Lehet, hogy egy kép közel van a nézethez.

A következőképpen kell használni:

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

eredmény

Hierarchikus adatok reprezentációja

Azt hiszem, valószínűleg ez a minta teszi a közös táblázatkifejezéseket olyan hasznossá. A korábban bevezetett adatok kiválasztott kifejezése helyettesíthető más dolgokkal, például nézetekkel és táblázatváltozókkal, így nem használja ki a sokat.

A hierarchikus reprezentáció úgy érhető el, hogy gyakoribb táblakifejezéseket hív meg a közös táblakifejezésekben, mint a következő SQL-ben:

A használat során óvatosnak kell lennie, hogy "kombinálja a kezdőpontot és a választógombot, amely összeköti a hierarchikus adatokat az összes egyesítéssel" és "a méret egyesítése a karakterláncok kombinálásakor".

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

eredmény

Aggregáció a gyakori táblakifejezések hierarchia-feldolgozásán belül (hiba történt)

Úgy gondolom, hogy van egy eset, amikor ki szeretné számítani az összesített értéket a közös táblakifejezés hierarchikus feldolgozásában. Itt megpróbáljuk kiszámítani az összegazonosítóhoz kapcsolódó pontok teljes számát.

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

De amikor futtatom, a következő hibaüzenetet kapom:

Úgy tűnik, hogy a csoportosítással kapcsolatos feldolgozást nem lehet rekurzív közös táblázatkifejezésbe helyezni.

Összesítés a közös táblázatkifejezések hierarchikus feldolgozásában (normál viselkedés)

Bár az összesítési folyamatot nem lehet leírni a rekurzív közös táblakifejezésben, az összesítés feldolgozási részének közös táblakifejezésként való meghatározásával végrehajtható.

Több gyakori táblakifejezés írásához válassza el a közös táblakifejezéseket vesszővel.

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

eredmény