Melakukan pemrosesan agregasi di dalam pemrosesan rekursif menggunakan ekspresi tabel umum

Contoh Data

Gunakan tabel dengan data hierarkis dan tabel berikut dengan data yang diringkas:

Tabel hierarki jumlah

Tabel Poin

Cara menggunakan ekspresi tabel umum

Saya tidak akan menulis terlalu banyak detail karena topik utamanya tidak ada di sini, tetapi untuk meringkasnya secara sederhana, ekspresi tabel umum seperti ekspresi pilihan yang telah Anda tentukan sebelumnya dan kemudian Anda dapat menggunakan ekspresi pilih yang telah Anda tentukan tanpa membuat beberapa deskripsi yang sama dalam referensi tabel selanjutnya. Ini mungkin gambar yang dekat dengan tampilan.

Berikut cara menggunakannya:

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

hasil

Representasi data hierarkis

Saya pikir mungkin pola inilah yang membuat ekspresi tabel umum sangat berguna. Ekspresi pilih data yang diperkenalkan sebelumnya dapat diganti dengan hal-hal lain seperti tampilan dan variabel tabel, sehingga tidak banyak dimanfaatkan.

Representasi hierarkis dapat dicapai dengan memanggil ekspresi tabel yang lebih umum dalam ekspresi tabel umum, seperti dalam SQL berikut:

Poin yang harus diperhatikan saat menggunakannya adalah "untuk menggabungkan pilihan yang merupakan titik awal dan pilihan yang menghubungkan data hierarkis dengan union all" dan "untuk menyatukan ukuran saat menggabungkan string".

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

hasil

Agregasi dalam pemrosesan hierarki ekspresi tabel umum (terjadi kesalahan)

Saya pikir ada kasus di mana Anda ingin menghitung nilai agregat dalam pemrosesan hierarkis dari ekspresi tabel umum. Di sini, kami mencoba menghitung jumlah total poin yang ditautkan ke 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 ketika saya menjalankannya, saya mendapatkan kesalahan berikut:

Tampaknya tidak mungkin untuk menempatkan pemrosesan terkait pengelompokan dalam ekspresi tabel umum rekursif.

Agregasi dalam Pemrosesan Hierarkis Ekspresi Tabel Umum (Perilaku Normal)

Meskipun tidak mungkin untuk menggambarkan proses agregasi dalam ekspresi tabel umum rekursif, itu dapat dijalankan dengan mendefinisikan bagian pemrosesan agregasi sebagai ekspresi tabel umum.

Untuk menulis beberapa ekspresi tabel umum, pisahkan ekspresi tabel umum 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]
;

hasil