SQL4 択難易度 標準

CTE を 2 つ（d と h）定義する書き方として正しいのは？

WITH d AS (SELECT * FROM emp WHERE dept = 'dev')WITH h AS (SELECT * FROM d WHERE salary >= 400)SELECT name FROM h;

正答

1. WITH d AS (SELECT * FROM emp WHERE dept='dev') AND h AS (SELECT * FROM d WHERE salary>=400) SELECT name FROM h;
2. WITH d AS (SELECT * FROM emp WHERE dept='dev'); WITH h AS (SELECT * FROM d WHERE salary>=400) SELECT name FROM h;
3. WITH d AS (SELECT * FROM emp WHERE dept='dev'), WITH h AS (SELECT * FROM d WHERE salary>=400) SELECT name FROM h;
4. WITH d AS (SELECT * FROM emp WHERE dept='dev'), h AS (SELECT * FROM d WHERE salary>=400) SELECT name FROM h;

解説

複数の CTE を定義するときは WITH を 1 回だけ書き、「名前 AS (問い合わせ)」をカンマで区切って並べる。後の CTE は前の CTE を参照できるので、h の中で d を使える。2 つ目にも WITH を付けるとカンマの有無にかかわらず構文エラーになる。AND でつなぐ形も文法にない。セミコロンで区切ると文がそこで終わり、本体の SELECT がない WITH だけの文になって失敗する。

参考: www.sqlite.org

withcte