Exécution du traitement d’agrégation à l’intérieur du traitement récursif à l’aide d’expressions de table courantes

Exemples de données

Utilisez des tables avec les données hiérarchiques suivantes et des tables avec des données résumées :

Tableau hiérarchique des montants

Tableau des points

Comment utiliser des expressions de table courantes

Je n’écrirai pas trop en détail car le sujet principal n’est pas ici, mais pour le résumer simplement, une expression de table commune est comme une expression de sélection que vous avez définie à l’avance et vous pouvez ensuite utiliser une expression de sélection que vous avez définie sans créer plusieurs de la même description dans une référence de table ultérieure. Il peut s’agir d’une image proche de la vue.

Voici comment l’utiliser :

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

résultat

Représentation des données hiérarchiques

Je pense que c’est probablement ce modèle qui rend les expressions de table courantes si utiles. L’expression de sélection des données introduites précédemment peut être substituée à d’autres éléments tels que les vues et les variables de table, de sorte qu’elle ne tire pas parti de grand-chose.

La représentation hiérarchique peut être obtenue en appelant des expressions de table plus courantes dans des expressions de table communes, comme dans le code SQL suivant :

Les points à surveiller lors de son utilisation sont « combiner la sélection qui est le point de départ et la sélection qui relie les données hiérarchiques avec l’union tout » et « unifier la taille lors de la combinaison de chaînes ».

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

résultat

Agrégation dans le traitement hiérarchique des expressions de table courantes (une erreur s’est produite)

Je pense qu’il y a un cas où vous voulez calculer la valeur agrégée dans le traitement hiérarchique de l’expression de table commune. Ici, nous essayons de calculer le nombre total de points liés à l’ID de montant.

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

Mais lorsque je l’exécute, j’obtiens l’erreur suivante:

Il semble qu’il ne soit pas possible de placer le traitement lié au regroupement dans une expression de table commune récursive.

Agrégation dans le traitement hiérarchique des expressions de table courantes (comportement normal)

Bien qu’il ne soit pas possible de décrire le processus d’agrégation dans l’expression de table commune récursive, il peut être exécuté en définissant la partie de traitement d’agrégation comme une expression de table commune.

Pour écrire plusieurs expressions de table courantes, séparez les expressions de table communes par des virgules.

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

résultat