Melakukan pemprosesan pengagregatan di dalam pemprosesan rekursif menggunakan ungkapan jadual biasa

Data Sampel

Gunakan jadual dengan data hierarki dan jadual berikut dengan data yang diringkaskan:

Jadual hierarki Amaun

Jadual Titik

Cara menggunakan ungkapan jadual biasa

Saya tidak akan menulis terlalu terperinci kerana topik utama tidak ada di sini, tetapi untuk meringkaskannya semata-mata, ungkapan jadual biasa adalah seperti ungkapan pilih yang telah anda tentukan terlebih dahulu dan kemudian anda boleh menggunakan ungkapan terpilih yang telah anda tentukan tanpa membuat beberapa penerangan yang sama dalam rujukan jadual kemudian. Ia mungkin imej yang dekat dengan pandangan.

Inilah cara menggunakannya:

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

Keputusan

Perwakilan data hierarki

Saya pikir itu mungkin pola ini yang membuat ekspresi tabel umum begitu berguna. Ungkapan pilih data yang diperkenalkan sebelum ini boleh digantikan dengan perkara lain seperti pandangan dan pembolehubah jadual, jadi ia tidak mengambil kesempatan daripada banyak.

Perwakilan hierarki boleh dicapai dengan memanggil ungkapan jadual yang lebih biasa dalam ungkapan jadual biasa, seperti dalam SQL berikut:

Perkara yang perlu berhati-hati apabila menggunakannya adalah "untuk menggabungkan pilihan yang merupakan titik permulaan dan pilihan yang menghubungkan data hierarki dengan kesatuan semua" dan "untuk menyatukan saiz apabila menggabungkan rentetan".

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

Keputusan

Pengagregatan dalam pemprosesan hierarki ungkapan jadual biasa (ralat berlaku)

Saya fikir terdapat kes di mana anda ingin mengira nilai agregat dalam pemprosesan hierarki ungkapan jadual biasa. Di sini, kami cuba mengira jumlah mata yang dikaitkan dengan jumlah 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]
;

Tetapi apabila saya menjalankannya, saya mendapat ralat berikut:

Nampaknya tidak mungkin untuk meletakkan pemprosesan yang berkaitan dengan kumpulan dalam ungkapan jadual biasa yang rekursif.

Pengagregatan dalam Pemprosesan Hierarki Ungkapan Jadual Biasa (Kelakuan Normal)

Walaupun tidak mungkin untuk menerangkan proses pengagregatan dalam ungkapan jadual biasa rekursif, ia boleh dilaksanakan dengan menentukan bahagian pemprosesan pengagregatan sebagai ungkapan jadual biasa.

Untuk menulis berbilang ungkapan jadual biasa, pisahkan ungkapan jadual biasa dengan koma.

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

Keputusan