CTE構文の使用方法

Unduh

Mode fokus

Ukuran font

Terakhir diperbarui: 2025-12-30 16:46:24

TDSQL-C for MySQLの5.7バージョンではCTE構文をサポートしていません。8.0バージョン以降の行ストアエンジンのみがCTE構文をサポートしますが、読み取り専用分析エンジンについては、5.7バージョンでも8.0バージョンでも、いずれもCTE構文を正常にサポートしています。
サポート状況
1.2404.x
1.2404.x バージョンでは、読み取り専用分析エンジンはノンリカーシブCTEのみをサポートします。使用時にはヒント /*+ MERGE() */を追加する必要があります。そうでないと、正しい実行計画を生成できない可能性があります。今後のバージョンでは、読み取り専用分析エンジンは段階的にリカーシブCTEをサポートする予定であり、またCTEの実行性能を最適化する予定です。
2.2410.x
2.2410.x バージョンでは、読み取り専用分析エンジンは引き続きノンリカーシブCTEのみをサポートしています。ただし、現在のバージョンではCTE構文を実行する際、ヒント /*+ MERGE() */を追加せずに正常に実行できます。また、2.2410.x バージョンではストリーミング実行方式もサポートされ、CTEの実行時の性能が大幅に最適化されました。
4.2506.x
4.2506.x バージョンでは、読み取り専用分析エンジンはリカーシブCTEをサポートします。
CTEの概要
共通テーブル式 (Common Table Expressions, CTE) は SQL 標準の一部であり、通常「WITH 句」と呼ばれます。CTE は SQL:1999 標準で初めて導入され、一時的な結果セットを定義するための簡潔で強力な方法を提供することを目的としています。これらの結果セットは単一のSQL文内で複数回参照でき、クエリの可読性と保守性を大幅に向上させます。
WITH句の使用例：
-- Start defining CTE.
WITH CustomerCTE AS (
    SELECT customer_id, first_name, last_name, email_address
    FROM customer
)
-- End defining CTE.
SELECT  *
FROM CustomerCTE; -- Reference the CTE.
CTEのメリット
複雑なSQLクエリにおいて、CTEは多くの利点を提供します。
クエリの簡素化：CTEは複雑なSQL文を整理・簡略化し、保守性を向上させます。例えば、同じサブクエリを複数回参照する必要がある場合、CTEはコードの重複を回避でき、クエリをより明確にします。
コードの可読性向上：意味のある名称を使用して中間結果を表現することで、SQLをより理解しやすくなります。
重複クエリの回避：CTEは一時的な結果セットを定義することを許可し、これらの結果セットは1つのSQL内で複数回参照できるため、同じ操作の重複実行を回避できます。
再帰クエリ：CTEは再帰クエリをサポートし、階層構造データ（例：従業員の組織構造）のクエリニーズを処理できます。ツリー構造データを処理する際に非常に有用です。
構文構造
CTEの構文構造は以下の通りです。
with_clause:
    WITH [RECURSIVE]
        cte_name [(col_name [, col_name] ...)] AS (subquery)
        [, cte_name [(col_name [, col_name] ...)] AS (subquery)] ...
パラメータ項目
説明
WITHキーワード
WITHキーワードはCTE定義の開始を示します。
[RECURSIVE]
オプションのキーワードであり、RECURSIVEが含まれている場合、CTE内で自身を参照することを許可し、再帰クエリを作成するために使用されます。
cte_name
CTEに指定された名前であり、後続のクエリで参照できます。
[(col_name [、col_name] ...)]
オプションの列名リストは、CTEの結果セットに列名を指定します。省略した場合、サブクエリの列名が使用されます。
AS (subquery)
CTE内部のサブクエリであり、CTEの内容を定義します。
カンマと追加のCTEs
1つのWITH句では、複数のCTEsをカンマで区切って定義できます。各追加のCTEは同じ構造に従います：cte_name [(col_name ...)] AS (subquery)。
非再帰CTE（Non-Recursive CTE）
非再帰CTEでは、CTEは他のテーブルまたは以前に定義されたCTEのみを参照し、自身を参照することはありません。これは、複数ステップのクエリ分解に適しており、中間レベルの計算を通じて最終的なクエリ結果を段階的に構築します。
WITH
	cte1 AS (SELECT * FROM t1, t2),
    cte2 AS (SELECT i1, i2 FROM cte1 WHERE i3 > 10)
    cte3 AS (SELECT * FROM cte2, t3 WHERE cte2.i1 = t3.i1)
SELECT * FROM cte3;
リカーシブCTE（Recursive CTE）
再帰CTEでは、CTEが自身を参照します。Recursive CTEは、階乗計算、シーケンス生成、階層関係の走査など、ツリー構造やグラフ構造のデータクエリ処理に頻繁に使用されます。
リカーシブCTEは、シードパートサブクエリ、結合種別、リカーシブパートサブクエリの3つの部分で構成されます。シードパートサブクエリは自身を参照せず、リカーシブパートサブクエリは必ず自身を参照します。
WITH RECURSIVE cte(n, fact) AS (
    SELECT 0, 1 -- Seed Part Subquery
    UNION ALL -- Union Type
    SELECT n + 1, (n + 1) * fact FROM cte WHERE n < 5 -- Recursive Part Subquery
)
SELECT n, fact FROM cte;
例
階乗の計算
WITH RECURSIVE cte(n, fact) AS (
    SELECT 0, 1
    UNION ALL
    SELECT n + 1, (n + 1) * fact FROM cte WHERE n < 5
)
SELECT n, fact FROM cte;
この例では、再帰パート UNION ALL SELECT n + 1, (n + 1) * fact FROM cte WHERE n < 5 は自身を繰り返し呼び出し、nが5に達するまで続けます。再帰パートが空行を出力すると、再帰を終了します。
ツリー構造の走査
従業員の階層関係を含むemployeesテーブルがあると仮定します。idは従業員の一意識別子、nameは従業員名、manager_idはその従業員の上司の従業員idです。
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    manager_id INT
);
﻿
INSERT INTO employees (id, name, manager_id) VALUES
(1, 'CEO', NULL),
(2, 'Manager 1', 1),
(3, 'Manager 2', 1),
(4, 'Employee 1', 2),
(5, 'Employee 2', 2),
(6, 'Employee 3', 3);
再帰CTEは、従業員の階層構造を走査し、上から下へすべての部下を取得するために使用されます。
﻿
WITH RECURSIVE employee_hierarchy AS (
    -- 基本ケース：CEOから開始
    SELECT 
        id, 
        name, 
        manager_id, 
        1 AS level
    FROM employees
    WHERE manager_id IS NULL
﻿
    UNION ALL
﻿
    -- 再帰ケース：各従業員の部下を特定
    SELECT 
        e.id, 
        e.name, 
        e.manager_id, 
        eh.level + 1
    FROM employees e
    INNER JOIN employee_hierarchy eh ON eh.id = e.manager_id
)
SELECT id, name, manager_id, level
FROM employee_hierarchy
ORDER BY level, manager_id;
﻿
-- Result
┌───────┬────────────┬────────────┬───────┐
│  id   │    name    │ manager_id │ level │
│ int32 │  varchar   │   int32    │ int32 │
├───────┼────────────┼────────────┼───────┤
│     1 │ CEO        │            │     1 │
│     2 │ Manager 1  │          1 │     2 │
│     3 │ Manager 2  │          1 │     2 │
│     4 │ Employee 1 │          2 │     3 │
│     5 │ Employee 2 │          2 │     3 │
│     6 │ Employee 3 │          3 │     3 │
└───────┴────────────┴────────────┴───────┘
基本CTE
WITH CustomerCTE AS (
    SELECT customer_id, first_name, last_name, email_address
    FROM customer
)
SELECT /*+ MERGE() */ * 
FROM CustomerCTE;
複数のCTE
WITH 
CTE1 AS (
    SELECT customer_id, first_name, last_name, email_address
    FROM customer
),
CTE2 AS (
    SELECT ss_item_sk, ss_customer_sk, ss_sold_date_sk, ss_sales_price
    FROM store_sales
)
SELECT /*+ MERGE() */ CTE1.first_name, CTE1.last_name, CTE2.ss_sales_price
FROM CTE1
JOIN CTE2 ON CTE1.customer_id = CTE2.ss_customer_sk;
2つのCTE（CTE1とCTE2）を定義します。
これら2つのCTEの結果セットを使用して、最終クエリで結合操作を実行します。
実行結果：
+------------+-----------+----------------+
| first_name | last_name | ss_sales_price |
+------------+-----------+----------------+
| John       | Doe       |          45.99 |
| Jane       | Smith     |          32.50 |
| Michael    | Johnson   |          78.25 |
| Emily      | Brown     |          19.99 |
| David      | Wilson    |          55.00 |
| John       | Doe       |          67.75 |
| Jane       | Smith     |          22.99 |
| Michael    | Johnson   |          41.50 |
| Emily      | Brown     |          89.99 |
| David      | Wilson    |          33.25 |
+------------+-----------+----------------+
10 rows in set (0.12 sec)
ネストCTE
WITH SalesSummary AS (
    SELECT ss_customer_sk, SUM(ss_net_paid) AS total_spent
    FROM store_sales
    GROUP BY ss_customer_sk
),
TopCustomers AS (
    SELECT ss_customer_sk, total_spent
    FROM SalesSummary
    WHERE total_spent > 1000  -- しきい値を設定する想定（例：1000を超える消費額の顧客）
),
CustomerDetails AS (
    SELECT c.customer_id, c.first_name, c.last_name, tc.total_spent
    FROM customer c
    JOIN TopCustomers tc ON c.customer_id = tc.ss_customer_sk
)
SELECT /*+ MERGE() */ * 
FROM CustomerDetails;
SalesSummaryは各顧客の総消費額を計算します。
TopCustomersは、SalesSummary結果セットから消費額が1000を超える顧客を抽出します。
CustomerDetailsは、customerテーブルの顧客情報とTopCustomersの結果セットを結合します。
最終のSELECTクエリはCustomerDetailsからすべてのデータを取得します。
実行結果：
+-------------+------------+-----------+--------------+
| customer_id | first_name | last_name | total_spent  |
+-------------+------------+-----------+--------------+
|        1001 | John       | Doe       |      1523.75 |
|        1002 | Jane       | Smith     |      2105.50 |
|        1003 | Michael    | Johnson   |      1789.99 |
|        1004 | Emily      | Brown     |      1650.25 |
|        1005 | David      | Wilson    |      1875.00 |
|        1006 | Sarah      | Davis     |      2250.75 |
|        1007 | Robert     | Taylor    |      1955.50 |
|        1008 | Jennifer   | Anderson  |      1725.25 |
|        1009 | William    | Thomas    |      2015.00 |
|        1010 | Lisa       | Jackson   |      1890.75 |
+-------------+------------+-----------+--------------+
10 rows in set (0.15 sec)

Bantuan dan Dukungan

Apakah halaman ini membantu?

Anda juga dapat Menghubungi Penjualan atau Mengirimkan Tiket untuk meminta bantuan.

masukan

パラメータ項目	説明
WITHキーワード	WITHキーワードはCTE定義の開始を示します。
[RECURSIVE]	オプションのキーワードであり、RECURSIVEが含まれている場合、CTE内で自身を参照することを許可し、再帰クエリを作成するために使用されます。
cte_name	CTEに指定された名前であり、後続のクエリで参照できます。
[(col_name [、col_name] ...)]	オプションの列名リストは、CTEの結果セットに列名を指定します。省略した場合、サブクエリの列名が使用されます。
AS (subquery)	CTE内部のサブクエリであり、CTEの内容を定義します。
カンマと追加のCTEs	1つのWITH句では、複数のCTEsをカンマで区切って定義できます。各追加のCTEは同じ構造に従います：cte_name [(col_name ...)] AS (subquery)。

tencent cloud

TDSQL-C for MySQL

CTE構文の使用方法

サポート状況

CTEの概要

CTEのメリット

構文構造

非再帰CTE（Non-Recursive CTE）

リカーシブCTE（Recursive CTE）

例

基本CTE

複数のCTE

ネストCTE

Bantuan dan Dukungan