共通テーブル式（CTE）の復習

SQL Server における階層型クエリと再帰クエリ

Jasmin Ludolf

Content Developer

コース概要

再帰とは何か。どう使うか。
階層・再帰クエリにCTE（共通テーブル式）をどう使うか。
SQLで階層をどう表現し、どう問い合わせるか。
実例（会社組織、部品表、フライト計画など）

入れ子構造を示すマトリョーシカの画像。

CTEとは

CTEの定義

一時的な名前付き結果セット（CTE）を指定します。これは単純なクエリから導出され、1つのステートメントの実行スコープ内で定義されます。

CTEの構造

WITH CTEtable as 
(
    -- a query --
)

SELECT *
    FROM CTEtable

CTEの用途

複雑なクエリを整理
SELECT、INSERT、UPDATE、DELETEで利用可
1つのWITHで複数のCTEを定義可
複数のCTEをUNIONやJOINで結合
ビューの代替として使用
テーブルの自己参照
再帰クエリ

IT組織向けのCTEを定義する

CTEでマネージャーを選択:

WITH JOBS (id, SurName, JobTitle) AS

(
    SELECT 
          ID, 
          Name,
          Position
    FROM employee
    WHERE Position like '%Manager%'
),

CTEを定義するWITH AS
EmployeeテーブルへのSELECT

IT組織向けのCTEを定義する

給与が10,000超の従業員を選択

SALARIES (ID,Salary) AS (
   SELECT
        ID,
        Salary
   FROM ITSalary
   WHERE Salary > 10000)

IT組織向けのCTEを定義する

クエリ全体:

WITH JOBS (id, SurName, JobTitle) AS 
(    SELECT ID, Name, Position
        FROM employee 
        WHERE Position like '%Manager%'),

SALARIES (ID,Salary) AS
(    SELECT ID, Salary
        FROM ITSalary 
        WHERE Salary > 10000)

SELECT JOBS.Name, JOBS.Position, SALARIES.Salary
    FROM JOBS
    INNER JOIN SALARIES
    on JOBS.ID = SALARIES.ID;

SurNmames     | JobTitle          | Salary
Paul Smith    | IT Manager        | 15,000
Adam Peterson | Sourcing Manager  | 12,500
Anna Nilson   | Portfolio Manager | 10,500

練習に進みましょう！

SQL Server における階層型クエリと再帰クエリ