WITH句を使って共通化テーブルを作る
WITH句を使用してサブクエリに名前を付けておき、共通化することによって可読性が高まり、また、パフォーマウンスの向上にもつながる。
SQLをVIEWのように定義しておき、そのテーブルを参照することができる。
例)
id | 科目 | 名前 | 点数 |
---|---|---|---|
1 | 数学 | 田中さん | 85点 |
2 | 国語 | 鈴木さん | 80点 |
3 | 英語 | 山田さん | 88点 |
1 | 英語 | 田中さん | 78点 |
2 | 英語 | 鈴木さん | 70点 |
SELECT id,科目,名前,点数
FROM TABLEA TA
LEFT JOIN (SELECT id,SUM(点数) AS 合計
FROM TABLEA
GROUP BY id) TAG
ON TA.id = TAG.id
結果:
id | 科目 | 名前 | 点数 | 合計 |
---|---|---|---|---|
1 | 数学 | 田中さん | 85点 | 163点 |
2 | 国語 | 鈴木さん | 80点 | 150点 |
3 | 英語 | 山田さん | 88点 | 88点 |
1 | 英語 | 田中さん | 78点 | 163点 |
2 | 英語 | 鈴木さん | 70点 | 150点 |
上記クエリにWITH句を使用すると、
WITH TAG AS
(SELECT id,SUM(点数) AS カウント
FROM TABLEA
GROUP BY code
)
SELECT id,科目,名前,点数
FROM TABLEA TA
LEFT JOIN TAG
ON TA.id = TAG.id
と書ける。
複数のテーブルを指定する
複数のサブクエリに対して使う場合は、カンマで区切って指定する。
また、自身の副問合せより前に定義したWITH句のテーブルを参照できる。
SELECT id,科目,名前,点数
FROM TABLEA TA
LEFT JOIN (SELECT id,SUM(点数) AS 合計
FROM TABLEA
GROUP BY id) TAG
ON TA.id = TAG.id
LEFT JOIN (SELECT 科目,AVG(点数) AS 科目平均
FROM TABLEA
GROUP BY 科目) TAA
ON TA.科目 = TAA.科目
結果:
id | 科目 | 名前 | 点数 | 合計 | 科目平均 |
---|---|---|---|---|---|
1 | 数学 | 田中さん | 85点 | 163点 | 85点 |
2 | 国語 | 鈴木さん | 80点 | 150点 | 80点 |
3 | 英語 | 山田さん | 88点 | 88点 | 78.7点 |
1 | 英語 | 田中さん | 78点 | 163点 | 78.7点 |
2 | 英語 | 鈴木さん | 70点 | 150点 | 78.7点 |
上記クエリにWITH句を使用すると、
WITH TAG AS
(SELECT id,SUM(点数) AS カウント
FROM TABLEA
GROUP BY code
)
,TAA AS
(SELECT 科目,AVG(点数) AS 科目平均
FROM TABLEA
GROUP BY 科目
)
SELECT id,科目,名前,点数
FROM TABLEA TA
LEFT JOIN TAG
ON TA.id = TAG.id
LEFT JOIN TAA
ON TA.id = TAA.id
また、自身より前に定義したテーブルの参照
WITH TAG AS
(SELECT id,SUM(点数) AS カウント
FROM TABLEA
GROUP BY code
)
,TAA AS
(SELECT 科目,AVG(点数) AS 科目平均
FROM TABLEA
GROUP BY 科目
)
,TAB AS
(SELECT カウント
FROM TAG
WHERE id = '1'
)
SELECT *
FROM TAB
同じテーブルを複数回指定する場合に、WITH句を使うと有効。
今日はここまで。