SQL SERVER 2005バージョン以上のCTE再帰クエリの実装

24498 ワード
sql server 2005
今日は基本的にSQL Serverの再帰クエリの実現方法を明らかにして、So、先に記録します.しかし、個人的にはSQL Serverの再帰クエリは、Oracleの再帰クエリよりも理解しにくいと思います.
SQL Server 2005からは、ツリーまたは階層のデータをクエリーするのに役立つCTEを直接使用して再帰クエリーをサポートできます.CTEは共通テーブル式であり、適切ではありませんが、一時的に命名された結果の集合と見なすことができます.
まず、メニューの階層を表すMENUという名前のサンプルテーブルを作成します.

 
   
    
  
    
  1. CREATE TABLE MENU   
    2. 
  
    
  2.  (  
    3. 
  
    
  3.     name nvarchar(50) NOT NULL PRIMARY KEY,  
    4. 
  
    
  4.     senior nvarchar(50) NULL 
    5. 
  
    
  5. );  
    6. 
  
    
  6.    
    7. 
  
    
  7.  INSERT INTO MENU values 
    8. 
  
    
  8.     ('  ',NULL),  
    9. 
  
    
  9.     ('  ','  '),  
    10. 
  
    
  10.     ('  ','  '),  
    11. 
  
    
  11.     ('        ','  '); 
    12.

表示されるメニュー階層は次のとおりです.

 
   
    
  
    
  1.      
    2. 
  
    
  2.          
    3. 
  
    
  3.              
    4. 
  
    
  4.                   
    5. 
  
    
  5.  
    6. 
  
    
  6. OK，  CTE   ：   
    7. 
  
    
  7.   WITH CTE  (    )   
    8. 
  
    
  8.   AS   
    9. 
  
    
  9.   (   
    10. 
  
    
  10.            
    11. 
  
    
  11.   ) 
    12.

 
例:

 
   
    
  
    
  1. WITH lmenu(name,senior)   
    2. 
  
    
  2. as   
    3. 
  
    
  3. (   
    4. 
  
    
  4.     SELECT name,senior from menu   
    5. 
  
    
  5. ) 
    6.

lmenuという名前のCTEを定義し、その後、lmenuを直接使用してクエリーできます.たとえば、次のようにします.

 
   
    
  
    
  1. SELECT * FROM lmenu 
    2.

CTEを定義するクエリー文でCTEテーブル自体を直接参照すると、再帰クエリーが形成されます.もちろん、再帰クエリーには独自の特殊な構造があります.次のSQLは、再帰クエリーによって各メニューの階層の深さを取得します.

 
   
    
  
    
  1. WITH lmenu(name,senior,level) as 
    2. 
  
    
  2.  (  
    3. 
  
    
  3.     SELECT NAME,SENIOR,0 level FROM MENU WHERE SENIOR IS NULL 
    4. 
  
    
  4.     UNION ALL 
    5. 
  
    
  5.     SELECT A.NAME,A.SENIOR,b.level+1 FROM MENU A,lmenu b  
    6. 
  
    
  6.     where a.senior = b.name 
    7. 
  
    
  7. )  
    8. 
  
    
  8.  
    9. 
  
    
  9. SELECT *  from lmenu 
    10.

結果:
  

 
   
    
  
    
  1. name               senior              level   
    2. 
  
    
  2. ----------------------------------- -----------   
    3. 
  
    
  3.                   NULL                 0   
    4. 
  
    
  4.                                       1   
    5. 
  
    
  5.                                  2   
    6. 
  
    
  6.                                      2 
    7.

クエリ定義文は、2つのクエリ文で構成されていることに注意してください.

 
   
    
  
    
  1. SELECT NAME,SENIOR,0 level FROM MENU WHERE SENIOR IS NULL 
    2.

配置メンバーと呼ばれ、SQL Serverはこの文で再帰を続行するかどうかを判断します.

 
   
    
  
    
  1. SELECT A.NAME,A.SENIOR,b.level+1 FROM MENU A,lmenu b   
    2. 
  
    
  2.     where a.senior = b.name 
    3.

再帰メンバーと呼ばれ、from句でCTEオブジェクト自体が参照されていることを特徴とします.
再帰CTEはいくつかの制限条件(MSDNから引用)を有する.
少なくとも1つのロケーションポイントメンバーと1つの再帰メンバーがあります.もちろん、複数のロケーションポイントメンバーと再帰メンバーを定義できますが、すべてのロケーションポイントメンバーは再帰メンバーの前にある必要があります.
位置決めポイントメンバー間ではUNIOALL、UNION、INTERSECT、EXCEPT集合演算子を使用する必要があり、最後の位置決めポイントメンバーと再帰メンバー間ではUNIOALLを使用する必要があり、再帰メンバー間でもUNIOALL接続を使用する必要があります
位置決めポイントメンバーと再帰メンバーのフィールド数とタイプは完全に一致する必要があります.
再帰メンバーのFROM句はCTEオブジェクトを一度しか参照できません
再帰メンバーでは、次の項目は許可されていません.

 
   
    
  
    
  1. SELECT DISTINCT   
    2. 
  
    
  2. GROUP BY   
    3. 
  
    
  3. HAVING   
    4. 
  
    
  4.        
    5. 
  
    
  5. TOP   
    6. 
  
    
  6. LEFT、RIGHT、OUTER JOIN（     INNER JOIN）   
    7. 
  
    
  7.     
    8.

CTE再帰クエリの実行方法:
再帰的な終了は、ポイント・メンバーの位置決めに依存し、それが理解されると、再帰クエリの実行方法も理解されます.
上記の例の実行手順を見てみましょう.

 
   
    
  
    
  1. SELECT * FROM lmenu 
    2.

この文は再帰クエリに入ります

 
   
    
  
    
  1. SELECT A.NAME,A.SENIOR,b.level+1 FROM MENU A,lmenu b   
    2. 
  
    
  2.     where a.senior = b.name 
    3.

最外層の文として、再帰的な第1層は、menuテーブルでクエリされたレコードの順序が次のようになると仮定して、MENUテーブルのレコードに基づいてループする必要があることは明らかです.

 
   
    
  
    
  1. name                                       senior   
    2. 
  
    
  2. --- --------------------------------------------------   
    3. 
  
    
  3.                                             NULL   
    4. 
  
    
  4.                                                    
    5. 
  
    
  5.                                     
    6. 
  
    
  6.                                                  
    7.

1番目のレコード:
まず、再帰に入るかどうかを判断します.ファイルは位置決めポイントメンバー結果セットに含まれており、再帰条件に合致しないため、再帰に入らず、直接位置決めポイントメンバーセットからレコードを返します.

 
   
    
  
    
  1. select name,senior,0 level from menu where senior is null and name='  ' 
    2.

  

 
   
    
  
    
  1. name                    senior           level   
    2. 
  
    
  2. --------------- ------------------------ -----------   
    3. 
  
    
  3.                         NULL                0 
    4.

第二条記録:
すなわちNAME='新規'であり、位置決めポイントのメンバー結果セットに記録がなく、再帰に入ります.
現在のローの値を再帰メンバーに持ち込むには、次の手順に従います.

 
   
    
  
    
  1. SELECT A.NAME,A.SENIOR,b.level+1 level FROM MENU A,lmenu b   
    2. 
  
    
  2.     where a.senior = b.name   
    3. 
  
    
  3.         AND a.senior = '  '   
    4. 
  
    
  4.         AND a.name='  ' 
    5.

再帰の関連条件はa.senior=b.nameであるため、b.name='ファイル'は、この条件で下位再帰に入ります.これは実際には最初のレコードの場合です.name='ファイル'は位置決め点の条件に合致するため、再帰は終了します.lmenu再帰メンバーをサブクエリで置き換えると、2番目のレコードのクエリ文は実際には次のようになります.

 
   
    
  
    
  1. SELECT a.name,a.senior,b.level+1 from menu a, (  
    2. 
  
    
  2.   select name,senior,0 level  from menu 
    3. 
  
    
  3. where senior is null and name='  ' 
    4. 
  
    
  4. ) b  
    5. 
  
    
  5. where a.senior=b.name 
    6. 
  
    
  6.     and a.senior = '  ' 
    7. 
  
    
  7.     and a.name='  ' 
    8.

name senior level----------------------------------------------------------------新規ファイル1
第三条記録:
NAME='現在の接続クエリーを使用する'は、同じように位置決めポイントの条件を満たしていないため、再帰的になります.

 
   
    
  
    
  1. SELECT A.NAME,A.SENIOR,b.level+1 FROM MENU A,lmenu b   
    2. 
  
    
  2.     where a.senior = b.name   
    3. 
  
    
  3.           AND a.senior = '  '   
    4. 
  
    
  4.           AND a.name = '        ' 
    5.

同様に、現在のレコード条件を代入し、下位再帰b.name='新規'であり、'新規'は位置決め点条件に合致しないため、再帰を継続し、lmenu bはサブクエリを表す.

 
   
    
  
    
  1. select c.name,c.senior,d.level+1 level from menu c,lmenu d   
    2. 
  
    
  2. where c.senior = d.name   
    3. 
  
    
  3.      and c.name = '  '   
    4. 
  
    
  4.      and c.senior = '  ' 
    5.

上記の文に置き換えると、d.name='ファイル'は、再帰を継続する必要があるかどうかを再判断し、'ファイル'は再帰を終了する条件に合致するため、再帰を終了します.
サブクエリを使用して、3番目のレコードの再帰プロセスを次のように表します.

 
   
    
  
    
  1. SELECT a.name,a.senior,b.level+1 level FROM menu A,(  
    2. 
  
    
  2.         select c.name,c.senior,d.level+1 level from menu c,(  
    3. 
  
    
  3.           select name,senior,0 level from menu where senior is null and name='  ' 
    4. 
  
    
  4.     ) d  
    5. 
  
    
  5.     where c.senior = d.name 
    6. 
  
    
  6.         and c.name = '  ' 
    7. 
  
    
  7.         and c.senior = '  '     
    8. 
  
    
  8. ) b  
    9. 
  
    
  9. where a.senior = b.name 
    10. 
  
    
  10.     and a.senior = '  ' 
    11. 
  
    
  11.     and a.name = '        ' 
    12.

name                                     senior                    level
--------------------------------------------------------- -----------
現在の接続クエリーを使用して新規2を作成
第4条記録は第3条記録の再帰階層と全く同じである.
[タイプスクリプト]タイプチェック
TIL 12 l Javascript Test