Εκτέλεση επεξεργασίας συνάθροισης μέσα σε αναδρομική επεξεργασία χρησιμοποιώντας κοινές παραστάσεις πίνακα

Δείγμα δεδομένων

Χρησιμοποιήστε πίνακες με τα ακόλουθα ιεραρχικά δεδομένα και πίνακες με συνοπτικά δεδομένα:

Πίνακας ιεραρχίας ποσών

Πίνακας σημείου

Πώς να χρησιμοποιήσετε κοινές εκφράσεις πίνακα

Δεν θα γράψω με πάρα πολλές λεπτομέρειες, επειδή το κύριο θέμα δεν είναι εδώ, αλλά για να το συνοψίσω απλά, μια κοινή παράσταση πίνακα είναι σαν μια παράσταση επιλογής που έχετε ορίσει εκ των προτέρων και στη συνέχεια μπορείτε να χρησιμοποιήσετε μια παράσταση επιλογής που έχετε ορίσει χωρίς να δημιουργήσετε πολλές από τις ίδιες περιγραφές σε μια μεταγενέστερη αναφορά πίνακα. Μπορεί να είναι μια εικόνα κοντά στην προβολή.

Δείτε πώς μπορείτε να το χρησιμοποιήσετε:

-- 共通テーブル式で事前に 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]
;

αποτέλεσμα