MySQLの勉強：共通表形式CTE CommonTableExpression

2023-11-24 記載
概要 : MySQL8.0で新規導入の共通表形式CTEを学ぶ
関連記事 : MySQLの勉強：WINDOW関数
Keyword : 共通表形式CTE, MySQL

auto_id	区分	品名	価格
1	主食	白ごはん	200
2	汁物	季節の味噌汁	80
3	主食	かやくご飯	250
4	メイン皿	ハンバーグ	400
5	メイン皿	焼き魚	300
6	メイン皿	からあげ	350
7	副菜	冷奴	200
8	副菜	煮物	240
9	副菜	納豆	100
10	副菜	だし巻き卵	130
13	メイン皿	おつくり	400
14	メイン皿	ステーキ	500

上記テーブルに以下のWINDOW関数SQLを発行して下の表が取得できました。

select * , RANK() 
over (PARTITION by 区分 ORDER by 価格) as 安い順 
from t_test;

auto_id	区分	品名	価格	安い順
5	メイン皿	焼き魚	300	1
6	メイン皿	からあげ	350	2
13	メイン皿	おつくり	400	3
4	メイン皿	ハンバーグ	400	3
14	メイン皿	ステーキ	500	5
1	主食	白ごはん	200	1
3	主食	かやくご飯	250	2
9	副菜	納豆	100	1
10	副菜	だし巻き卵	130	2
7	副菜	冷奴	200	3
8	副菜	煮物	240	4
2	汁物	季節の味噌汁	80	1

ここから一番安いものだけを抽出するSQLを

select * , RANK() 
over (PARTITION by 区分 ORDER by 価格) as 安い順 
from t_test where 安い順=1;

と書けばエラーになります。
#1054 - 列 '安い順' は 'where clause' にはありません。

こういう場合にCTEを用いて以下のように書いて目的のビューを取得することができました。

with cte_g1 as (select * , 
RANK() over (PARTITION by 区分 ORDER by 価格) as 安い順 from t_test) 
select * from cte_g1 where 安い順=1;

auto_id	区分	品名	価格	安い順
5	メイン皿	焼き魚	300	1
1	主食	白ごはん	200	1
9	副菜	納豆	100	1
2	汁物	季節の味噌汁	80	1

サイト管理人Blog