Thực hiện xử lý tổng hợp bên trong xử lý đệ quy bằng cách sử dụng biểu thức bảng phổ biến

Dữ liệu Mẫu

Dùng bảng có dữ liệu và bảng phân cấp sau đây với dữ liệu tóm tắt:

Bảng phân cấp số tiền

Bảng Điểm

Cách sử dụng biểu thức bảng thông thường

Tôi sẽ không viết quá nhiều chi tiết vì chủ đề chính không ở đây, nhưng để tóm tắt nó một cách đơn giản, một biểu thức bảng chung giống như một biểu thức được chọn mà bạn đã xác định trước và sau đó bạn có thể sử dụng một biểu thức được chọn mà bạn đã xác định mà không tạo ra nhiều mô tả giống nhau trong tham chiếu bảng sau này. Nó có thể là một hình ảnh gần với tầm nhìn.

Đây là cách sử dụng nó:

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

kết quả

Đại diện cho dữ liệu phân cấp

Tôi nghĩ rằng có lẽ mô hình này làm cho các biểu thức bảng phổ biến rất hữu ích. Biểu thức chọn của dữ liệu được giới thiệu trước đó có thể được thay thế cho những thứ khác như dạng xem và biến bảng, vì vậy nó không tận dụng được nhiều.

Đại diện phân cấp có thể đạt được bằng cách gọi các biểu thức bảng phổ biến hơn trong các biểu thức bảng chung, như trong SQL sau đây:

Các điểm cần cẩn thận khi sử dụng nó là "kết hợp lựa chọn đó là điểm bắt đầu và lựa chọn kết nối dữ liệu phân cấp với liên minh tất cả" và "để thống nhất kích thước khi kết hợp chuỗi".

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

kết quả

Tổng hợp trong hệ thống phân cấp xử lý các biểu thức bảng phổ biến (lỗi đã xảy ra)

Tôi nghĩ rằng có một trường hợp bạn muốn tính toán giá trị tổng hợp trong quá trình xử lý phân cấp của biểu thức bảng chung. Ở đây, chúng tôi đang cố gắng tính toán tổng số điểm được liên kết với ID số tiền.

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

Nhưng khi tôi chạy nó, tôi nhận được lỗi sau:

Có vẻ như không thể đặt xử lý liên quan đến nhóm trong một biểu thức bảng chung đệ quy.

Tổng hợp trong xử lý phân cấp các biểu thức bảng thông thường (Hành vi bình thường)

Mặc dù không thể mô tả quá trình tổng hợp trong biểu thức bảng chung đệ quy, nhưng nó có thể được thực hiện bằng cách xác định phần xử lý tổng hợp như một biểu thức bảng chung.

Để viết nhiều biểu thức bảng phổ biến, hãy tách các biểu thức bảng chung bằng dấu phẩy.

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

kết quả