Yanor.net/
Wiki
Blog
GitHub
Sandbox
開始行:
* 共通表式 WITH句 [#bfc0ad13]
** 前提 [#r77ef48b]
SELECT * FROM t1;
id1 | id2
-----+-----
1 | 2
3 | 4
5 | 6
(3 rows)
SELECT * FROM t2;
id
----
1
3
(2 rows)
** WITHを使わない [#a79e0c4e]
SELECT * FROM t1 WHERE id1 IN (SELECT id FROM t2 WHERE i...
id1 | id2
-----+-----
3 | 4
(2 rows)
** WITHを使う [#k66c1e45]
WITH _t2 AS (SELECT id FROM t2 WHERE id > 1)
SELECT * FROM t1 WHERE id1 IN (SELECT id FROM _t2) OR id...
id1 | id2
-----+-----
3 | 4
(1 row)
** WITHでDELETE [#y5a0de08]
SELECT * FROM t1;
id
-----
100
200
(2 rows)
SELECT * FROM t1_backup ;
id
----
(0 rows)
WITH del AS (DELETE FROM t1 RETURNING id) INSERT INTO t1...
SELECT * FROM t1;
id
----
(0 rows)
SELECT * FROM t1_backup ;
id
-----
100
200
(2 rows)
** 参考 [#x511c25a]
http://lets.postgresql.jp/documents/technical/with_recurs...
終了行:
* 共通表式 WITH句 [#bfc0ad13]
** 前提 [#r77ef48b]
SELECT * FROM t1;
id1 | id2
-----+-----
1 | 2
3 | 4
5 | 6
(3 rows)
SELECT * FROM t2;
id
----
1
3
(2 rows)
** WITHを使わない [#a79e0c4e]
SELECT * FROM t1 WHERE id1 IN (SELECT id FROM t2 WHERE i...
id1 | id2
-----+-----
3 | 4
(2 rows)
** WITHを使う [#k66c1e45]
WITH _t2 AS (SELECT id FROM t2 WHERE id > 1)
SELECT * FROM t1 WHERE id1 IN (SELECT id FROM _t2) OR id...
id1 | id2
-----+-----
3 | 4
(1 row)
** WITHでDELETE [#y5a0de08]
SELECT * FROM t1;
id
-----
100
200
(2 rows)
SELECT * FROM t1_backup ;
id
----
(0 rows)
WITH del AS (DELETE FROM t1 RETURNING id) INSERT INTO t1...
SELECT * FROM t1;
id
----
(0 rows)
SELECT * FROM t1_backup ;
id
-----
100
200
(2 rows)
** 参考 [#x511c25a]
http://lets.postgresql.jp/documents/technical/with_recurs...
ページ名:
