Ortak tablo ifadelerini kullanarak özyinelemeli işleme içinde toplama işlemi gerçekleştirme

Örnek Veriler

Aşağıdaki hiyerarşik verilere sahip tabloları ve özetlenmiş verilere sahip tabloları kullanın:

Tutar hiyerarşisi tablosu

Nokta Tablosu

Ortak tablo ifadeleri nasıl kullanılır?

Ana konu burada olmadığı için çok fazla ayrıntıyla yazmayacağım, ancak basitçe özetlemek gerekirse, ortak bir tablo ifadesi, önceden tanımladığınız bir select ifadesi gibidir ve daha sonra daha sonraki bir tablo başvurusunda aynı açıklamanın birden katını oluşturmadan tanımladığınız bir select ifadesini kullanabilirsiniz. Görünüme yakın bir görüntü olabilir.

Bunu şu şekilde kullanabilirsiniz:

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

sonuç

Hiyerarşik verilerin temsili

Bence ortak tablo ifadelerini bu kadar kullanışlı kılan muhtemelen bu kalıptır. Daha önce tanıtılan verilerin seçme ifadesi, görünümler ve tablo değişkenleri gibi diğer şeylerle değiştirilebilir, bu nedenle fazla bir şeyden yararlanmaz.

Hiyerarşik gösterim, aşağıdaki SQL'de olduğu gibi, ortak tablo ifadelerinde daha yaygın tablo ifadeleri çağrılarak elde edilebilir:

Kullanırken dikkat edilmesi gereken noktalar "başlangıç noktası olan seçimi ve hiyerarşik verileri tümünü birleştir seçeneğiyle birleştiren seçimi birleştirmek" ve "dizeleri birleştirirken boyutu birleştirmek" tir.

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

sonuç

Ortak tablo ifadelerinin hiyerarşik işlenmesi içinde toplama (hata oluştu)

Ortak tablo ifadesinin hiyerarşik işlenmesinde toplanan değeri hesaplamak istediğiniz bir durum olduğunu düşünüyorum. Burada, miktar kimliğine bağlı toplam puan sayısını hesaplamaya çalışıyoruz.

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

Ancak çalıştırdığımda aşağıdaki hatayı alıyorum:

Gruplamayla ilgili işlemeyi özyinelemeli bir ortak tablo ifadesine koymak mümkün değil gibi görünüyor.

Ortak Tablo İfadelerinin Hiyerarşik İşlemesinde Toplama (Normal Davranış)

Özyinelemeli ortak tablo ifadesinde toplama işlemini açıklamak mümkün olmasa da, toplama işleme bölümünü ortak tablo ifadesi olarak tanımlayarak yürütülebilir.

Birden çok ortak tablo ifadesi yazmak için, ortak tablo ifadelerini virgülle ayırın.

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

sonuç