ביצוע עיבוד צבירה בתוך עיבוד רקורסיבי באמצעות ביטויי טבלה נפוצים

נתונים לדוגמה

השתמש בטבלאות עם הנתונים ההיררכיים והטבלאות הבאים עם נתונים מסוכמים:

טבלת הירארכיית הסכומים

טבלת נקודות

כיצד להשתמש בביטויי טבלה נפוצים

אני לא אכתוב בפירוט רב מדי כי הנושא העיקרי לא נמצא כאן, אבל כדי לסכם את זה בפשטות, ביטוי טבלה נפוץ הוא כמו ביטוי נבחר שהגדרת מראש ואז אתה יכול להשתמש בביטוי נבחר שהגדרת מבלי ליצור כפולות של אותו תיאור בהפניה מאוחרת יותר בטבלה. זו עשויה להיות תמונה קרובה לתצוגה.

כך תוכל להשתמש בו:

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

תוצאה

ייצוג נתונים היררכיים

אני חושב שזו כנראה התבנית הזו שהופכת ביטויי שולחן נפוצים לכל כך שימושיים. ניתן להחליף את הביטוי הנבחר של הנתונים שהוצגו קודם לכן בדברים אחרים כגון תצוגות ומשתני טבלה, כך שהוא אינו מנצל הרבה.

ניתן להשיג ייצוג הירארכי על-ידי קריאה לביטויי טבלה נפוצים יותר בביטויי טבלה נפוצים, כמו ב- SQL הבא:

נקודות שיש להיזהר מהן בעת השימוש בה הן "לשלב את הבחירה שהיא נקודת ההתחלה ואת הבחירה המחברת את הנתונים ההיררכיים עם איחוד הכל" ו"לאחד את הגודל בעת שילוב מחרוזות".

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

תוצאה

צבירה בתוך עיבוד ההיררכיה של ביטויי טבלה נפוצים (אירעה שגיאה)

אני חושב שיש מקרה שבו אתה רוצה לחשב את הערך המצטבר בעיבוד ההיררכי של ביטוי הטבלה המשותף. כאן, אנו מנסים לחשב את המספר הכולל של נקודות המקושרות לסכום מזהה.

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

אבל כאשר אני מפעיל אותו, אני מקבל את השגיאה הבאה:

נראה כי לא ניתן לשים עיבוד הקשור לקיבוץ בביטוי טבלה משותף רקורסיבי.

צבירה בעיבוד היררכי של ביטויי טבלה נפוצים (התנהגות נורמלית)

למרות שלא ניתן לתאר את תהליך הצבירה בביטוי הטבלה המשותפת הרקורסיבית, ניתן לבצע אותו על ידי הגדרת חלק עיבוד הצבירה כביטוי טבלה נפוץ.

כדי לכתוב ביטויי טבלה נפוצים מרובים, הפרד את ביטויי הטבלה הנפוצים באמצעות פסיקים.

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

תוצאה