SQL では、WITH 句は CTE とも呼ばれます。これは、クエリ内で一時的な結果セットを作成できる強力な機能です。 CTE の主な役割の 1 つは、複雑なクエリをより小さく再利用可能なサブクエリに単純化することです。これにより、コードが読みやすくなり、長期的には保守しやすくなります。
このチュートリアルに参加して、WITH 句を使用した共通テーブル式の仕組みとサポートされている機能を確認してください。
要件：
デモンストレーションの目的で、次のものを使用します。
- MySQL バージョン 8.0 以降
- サキラサンプルデータベース
所定の要件が満たされたら、CTE と WITH 句についてさらに学習することができます。
SQL WITH 句
WITH 句を使用すると、共通テーブル式と呼ばれる 1 つ以上の一時的な結果セットを定義できます。
他のテーブルや結果セットと同様に、メイン クエリで結果の CTE を参照できます。これは、モジュール式 SQL クエリを作成する際に重要な役割を果たします。
CTE の構文は要件に応じて若干異なる場合がありますが、SQL での CTE の基本構文を以下に示します。
WITH cte_name (column1、column2、...) AS (
-- CTE クエリ
選択する ...
から ...
どこ ...
)
-- メインクエリ
選択する ...
から ...
JOIN cte_name ON ...
どこ ...
まず、CTE を作成して使用することを SQL データベースに伝える WITH キーワードから始めます。
次に、他のクエリで参照できるようにする CTE の名前を指定します。
CTE に列の別名が含まれる場合は、列名のオプションのリストも指定します。
次に、CTE クエリの定義に進みます。これには、CTE が実行するすべてのタスクまたはデータが括弧で囲まれて含まれています。
最後に、CTE を参照するメイン クエリを指定します。
使用例:
CTE の使用方法と操作方法を理解する最良の方法の 1 つは、実際の例を見ることです。
例として、Sakila サンプル データベースを考えてみましょう。レンタル回数が最も多い上位 10 人の顧客を見つけたいとします。
次に示す CTE を見てください。
SQL WITH 句を使用して、レンタル数が最も多い上位 10 人の顧客を検索します。
CustomerRentals AS を使用 (SELECT c.customer_id、c.first_name、c.last_name、COUNT(r.rental_id) AS Rental_count
お客様c様より
レンタル r に参加します。 c.customer_id = r.customer_id
c.customer_id、c.first_name、c.last_name によるグループ化
)
選択する *
カスタマーレンタルから
ORDER BY Rental_count DESC
リミット10;
この例では、まず WITH キーワードを使用して新しい CTE を定義し、その後に CTE に割り当てる名前を続けます。この場合、それを「CustomerRentals」と呼びます。
CTE 本体内で、顧客テーブルとレンタルテーブルを結合することにより、各顧客のレンタル数を計算します。
最後に、メイン クエリで、CTE からすべての列を選択し、レンタル数に基づいて結果を並べ替え (降順)、出力を上位 10 行のみに制限します。
これにより、次の出力に示すように、レンタル数が最も多い顧客を取得できるようになります。
再帰的 CTE
他の場合には、階層データ構造を扱うこともあります。ここで再帰的 CTE が登場します。
たとえば、階層構造をナビゲートしたい、またはツリー状の構造を表現したい場合を考えてみましょう。 WITH RECURSIVE キーワードを使用して再帰 CTE を作成できます。
sakila データベースには再帰的 CTE を示すために使用できる階層データがないため、基本的な例を設定してみましょう。
CREATE TABLE 部門 (部門 ID INT 主キー AUTO_INCREMENT、
部門名 VARCHAR(255) NOT NULL、
親部門 ID INT、
外部キー (parent_Department_id) 参照部門 (Department_id)
);
INSERT INTO 部門 (部門名、親部門 ID)
価値観
(「法人」、NULL)、
(「金融」、1)、
('HR', 1),
(「会計」、2)、
「採用」、3)、
(「給与」、4);
この場合、ランダムなデータを含むサンプルの「部門」テーブルがあります。部門の階層構造を見つけるには、次のように再帰 CTE を使用できます。
再帰的DepartmentHierarchy ASを使用(SELECT 部門 ID、部門名、親部門 ID
部門から
WHERE 親部門 ID は NULL
すべてを結合する
SELECT d.部門ID、d.部門名、d.親部門ID
部門Dから
JOINDepartmentHierarchy dh ON d.parent_Department_id = dh.Department_id
)
選択する *
部門階層から;
この場合、再帰的 CTE は、NULL の「parent_Department_id」を持つ部門 (ルート部門) から開始し、子部門を再帰的に取得します。
結論
このチュートリアルでは、WITH キーワードの使い方を理解することで、共通テーブル式などの SQL データベースの最も基本的で便利な機能について学びました。