[解決済み] SQL Server CTEと再帰の例

質問

私は再帰でCTEを使用したことはありません。私はちょうどそれについての記事を読んでいました。この記事では、Sql server CTEと再帰の助けを借りて従業員情報を示しています。それは基本的に従業員とそのマネージャーの情報を表示します。私はこのクエリがどのように動作するかを理解することはできません。ここにクエリがあります。

WITH
  cteReports (EmpID, FirstName, LastName, MgrID, EmpLevel)
  AS
  (
    SELECT EmployeeID, FirstName, LastName, ManagerID, 1
    FROM Employees
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.EmployeeID, e.FirstName, e.LastName, e.ManagerID,
      r.EmpLevel + 1
    FROM Employees e
      INNER JOIN cteReports r
        ON e.ManagerID = r.EmpID
  )
SELECT
  FirstName + ' ' + LastName AS FullName,
  EmpLevel,
  (SELECT FirstName + ' ' + LastName FROM Employees
    WHERE EmployeeID = cteReports.MgrID) AS Manager
FROM cteReports
ORDER BY EmpLevel, MgrID

ここでは、出力がどのように表示されるかについて投稿しています。

私はそれが最初にマネージャを表示し、ループ内の彼の部下を表示している方法を知る必要があります。 最初のSQL文は一度だけ実行され、すべての従業員IDを返すのだと思います。

そして2番目のクエリは繰り返し実行され、現在のマネージャIDを持つ従業員が存在するデータベースにクエリを発行します。

sql文が内部ループでどのように実行されるかを説明し、またsqlの実行順序を私に教えてください。ありがとうございます。

MY第2段階の質問

;WITH Numbers AS
(
    SELECT n = 1
    UNION ALL
    SELECT n + 1
    FROM Numbers
    WHERE n+1 <= 10
)
SELECT n
FROM Numbers

Q 1) Nの値はどのようにインクリメントされるのでしょうか？ もし、毎回Nに値が割り当てられるのであれば、Nの値をインクリメントすることができますが、Nの値が最初に初期化されたときだけです。

Q 2) 社員関係のCTEと再帰について。

マネージャを2人追加し、2番目のマネージャの下に数人の従業員を追加した瞬間から問題が発生します。

最初のマネージャーの詳細を表示し、次の行にはそのマネージャーの部下に関連する従業員の詳細のみを表示したいのです。

仮に

ID     Name      MgrID    Level
---    ----      ------   -----
1      Keith      NULL     1
2      Josh       1        2
3      Robin      1        2
4      Raja       2        3
5      Tridip     NULL     1
6      Arijit     5        2
7      Amit       5        2
8      Dev        6        3

CTE式でこのように結果を表示させたいのですが。マネージャと従業員の関係を引き出すために、ここにあげた私のSQLのどこを修正すればよいか教えてください。ありがとうございます。

このような出力にしたいのですが。

ID          Name   MgrID       nLevel      Family
----------- ------ ----------- ----------- --------------------
1           Keith  NULL        1           1
3           Robin  1           2           1
2           Josh   1           2           1
4           Raja   2           3           1
5           Tridip NULL        1           2
7           Amit   5           2           2
6           Arijit 5           2           2
8           Dev    6           3           2

これは可能なのでしょうか...？

どのように解決するのですか？

あなたのコードをテストしたわけではありませんが、コメントでどのように動作するかを理解する手助けをしようとしただけです。

WITH
  cteReports (EmpID, FirstName, LastName, MgrID, EmpLevel)
  AS
  (
-->>>>>>>>>>Block 1>>>>>>>>>>>>>>>>>
-- In a rCTE, this block is called an [Anchor]
-- The query finds all root nodes as described by WHERE ManagerID IS NULL
    SELECT EmployeeID, FirstName, LastName, ManagerID, 1
    FROM Employees
    WHERE ManagerID IS NULL
-->>>>>>>>>>Block 1>>>>>>>>>>>>>>>>>
    UNION ALL
-->>>>>>>>>>Block 2>>>>>>>>>>>>>>>>>    
-- This is the recursive expression of the rCTE
-- On the first "execution" it will query data in [Employees],
-- relative to the [Anchor] above.
-- This will produce a resultset, we will call it R{1} and it is JOINed to [Employees]
-- as defined by the hierarchy
-- Subsequent "executions" of this block will reference R{n-1}
    SELECT e.EmployeeID, e.FirstName, e.LastName, e.ManagerID,
      r.EmpLevel + 1
    FROM Employees e
      INNER JOIN cteReports r
        ON e.ManagerID = r.EmpID
-->>>>>>>>>>Block 2>>>>>>>>>>>>>>>>>
  )
SELECT
  FirstName + ' ' + LastName AS FullName,
  EmpLevel,
  (SELECT FirstName + ' ' + LastName FROM Employees
    WHERE EmployeeID = cteReports.MgrID) AS Manager
FROM cteReports
ORDER BY EmpLevel, MgrID

最も単純な再帰的な例である CTE その動作を説明するために思いつくのは

;WITH Numbers AS
(
    SELECT n = 1
    UNION ALL
    SELECT n + 1
    FROM Numbers
    WHERE n+1 <= 10
)
SELECT n
FROM Numbers

Q 1) Nの値はどのようにインクリメントされるのでしょうか。もしNに毎回値が割り当てられるなら、Nの値はインクリメントされますが、Nの値が初期化されるのは最初の1回だけです。 .

A1: この場合 N は変数ではありません。 N はエイリアスである。と同等である。 SELECT 1 AS N . これは、個人的な好みの構文です。のカラムをエイリアスにする方法は、主に2つあります。 CTE で T-SQL . 私は、単純な CTE で Excel を使用して、何が起こっているのかをより身近な方法で説明することを試みます。

--  Outside
;WITH CTE (MyColName) AS
(
    SELECT 1
)
-- Inside
;WITH CTE AS
(
    SELECT 1 AS MyColName
    -- Or
    SELECT MyColName = 1  
    -- Etc...
)

<イグ

Q 2) 従業員関係のCTEと再帰についてです。 私は2つのマネージャーを追加し、2番目のマネージャーの下にいくつかのより多くの従業員を追加した瞬間に、問題が開始されます。 私は最初のマネージャーの詳細を表示し、次の行にのみ、それらの従業員の詳細は、そのマネージャーの部下である人が来るようにしたい。

A2:

このコードはあなたの質問に答えていますか？

--------------------------------------------
-- Synthesise table with non-recursive CTE
--------------------------------------------
;WITH Employee (ID, Name, MgrID) AS 
(
    SELECT 1,      'Keith',      NULL   UNION ALL
    SELECT 2,      'Josh',       1      UNION ALL
    SELECT 3,      'Robin',      1      UNION ALL
    SELECT 4,      'Raja',       2      UNION ALL
    SELECT 5,      'Tridip',     NULL   UNION ALL
    SELECT 6,      'Arijit',     5      UNION ALL
    SELECT 7,      'Amit',       5      UNION ALL
    SELECT 8,      'Dev',        6   
)
--------------------------------------------
-- Recursive CTE - Chained to the above CTE
--------------------------------------------
,Hierarchy AS
(
    --  Anchor
    SELECT   ID
            ,Name
            ,MgrID
            ,nLevel = 1
            ,Family = ROW_NUMBER() OVER (ORDER BY Name)
    FROM Employee
    WHERE MgrID IS NULL

    UNION ALL
    --  Recursive query
    SELECT   E.ID
            ,E.Name
            ,E.MgrID
            ,H.nLevel+1
            ,Family
    FROM Employee   E
    JOIN Hierarchy  H ON E.MgrID = H.ID
)
SELECT *
FROM Hierarchy
ORDER BY Family, nLevel

ツリー構造でもう一つのSQL

SELECT ID,space(nLevel+
                    (CASE WHEN nLevel > 1 THEN nLevel ELSE 0 END)
                )+Name
FROM Hierarchy
ORDER BY Family, nLevel