🦮
[SQL]共通テーブル式(Common Table Expressions、CTE)とは(備忘録)
1.はじめに
・共通テーブル式とは、一時的なクエリの実行結果を定義し、その定義結果を用い新たなクエリを実行する際に使用する構文です。
・共通テーブル式を利用すると、複雑なクエリを分かりやすくすることが可能で、ソースコードの可読性・保守性が向上します。
・共通テーブル式は、共通テーブルが定義されたクエリ内であれば何度も再利用可能です。
・WITH句を使用して、共通テーブル式を定義します。
共通テーブル式のクエリ例
WITH 共通テーブル名 AS (
SELECT
列1,
列2,
・・・
FROM テーブル名
WHERE ・・・
)
SELECT
列1,
列2、
・・・
FROM 共通テーブル名
;
2.サンプルプログラム
〇例1:
Sample_1.sql
CREATE TABLE employees (
employee_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(10),
department VARCHAR(10),
monthly_salary DECIMAL(10, 0)
);
INSERT INTO employees (
name,
department,
monthly_salary
)
VALUES
('田中', '営業', 400000), ('鈴木', '営業', 450000), ('山田', '開発', 650000)
,('大谷', '開発', 700000), ('高橋', '人事', 600000);
-- 共通テーブル式を定義したクエリ
WITH HighSalaryEmployees AS (
SELECT
name,
department,
monthly_salary
FROM employees
WHERE monthly_salary >= 600000
)
SELECT *
FROM HighSalaryEmployees
;
実行結果
name | department | monthly_salary |
---|---|---|
山田 | 開発 | 650000 |
大谷 | 開発 | 700000 |
高橋 | 人事 | 600000 |
〇例2:共通テーブルを使用して、テーブルを更新
Sample_2.sql
CREATE TABLE employees (
employee_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(10),
department_id INT,
monthly_salary DECIMAL(10, 0)
);
CREATE TABLE departments (
department_id INT AUTO_INCREMENT PRIMARY KEY,
department_name VARCHAR(10),
average_salary DECIMAL(10, 0)
);
INSERT INTO employees (name, department_id, monthly_salary)
VALUES
('田中', 1, 400000), ('鈴木', 1, 450000), ('山田', 2, 650000)
,('大谷', 2, 700000), ('高橋', 3, 600000),('山本', 3, 650000);
INSERT INTO departments (department_name, average_salary)
VALUES
('営業', 0),('開発', 0),('企画', 0);
-- 定義した共通式の内容を基に、他のテーブルを更新
WITH AvgSalary AS (
SELECT
department_id,
-- 部門毎の平均月収
AVG(monthly_salary) AS avg_salary
FROM employees
GROUP BY department_id
)
UPDATE departments AS a1
INNER JOIN AvgSalary AS a2
ON a1.department_id = a2.department_id
SET a1.average_salary = a2.avg_salary
;
-- 更新結果確認
SELECT * FROM departments;
実行結果
department_id | department_name | average_salary |
---|---|---|
1 | 営業 | 425000 |
2 | 開発 | 675000 |
3 | 企画 | 625000 |
3.参考
・[SQLの基本]一時テーブルとCTE(WITH句)入門: 効率的なデータ操作のためのツール
・mysqlでjoinした結果をupdate
4.その他
・実行環境
Discussion