> WITH RECURSIVE r(col) AS (
SELECT 'a'
UNION ALL
SELECT col || char(ascii(substr(col, -1)) + 1) FROM r WHERE LENGTH(col) < 10
)
SELECT * FROM r;
col
a
ab
abc
abcd
abcde
abcdef
abcdefg
abcdefgh
abcdefghi
abcdefghij
> WITH RECURSIVE t(n) AS (
SELECT 'foo'
UNION ALL
SELECT n || ' bar' FROM t WHERE length(n) < 20
)
SELECT n AS is_text FROM t;
is_text
foo
foo bar
foo bar bar
foo bar bar bar
foo bar bar bar bar
foo bar bar bar bar bar
> WITH RECURSIVE x(id) AS (VALUES (1) UNION ALL SELECT id+1 FROM x WHERE id < 5),
y(id) AS (VALUES (1) UNION ALL SELECT id+1 FROM x WHERE id < 10)
SELECT y.*, x.* FROM y LEFT JOIN x USING (id);
id id
1 1
4 4
6 null
3 3
5 5
2 2
> WITH RECURSIVE
y (id) AS (VALUES (1)),
x (id) AS (SELECT * FROM y UNION ALL SELECT id+1 FROM x WHERE id < 5)
SELECT * FROM x;
id
1
2
3
4
5
> CREATE OR REPLACE TEMPORARY VIEW nums AS
WITH RECURSIVE nums (n) AS (
VALUES (1)
UNION ALL
SELECT n+1 FROM nums WHERE n < 6
)
SELECT * FROM nums;
> SELECT * FROM nums;
n
__
1
2
3
4
5
6
> INSERT into a table
> CREATE TABLE rt(level INT);
> WITH RECURSIVE r(level) AS (
VALUES (0)
UNION ALL
SELECT level + 1 FROM r WHERE level < 9
)
INSERT INTO rt SELECT * FROM r;
> SELECT * from rt;
level
0
1
2
3
4
5
6
7
8
9
> WITH RECURSIVE t(j) AS (
WITH RECURSIVE s(i) AS (
VALUES (1)
UNION ALL
SELECT i+1 FROM s WHERE i < 10
)
SELECT i FROM s
UNION ALL
SELECT j+1 FROM t WHERE j < 10
)
SELECT * FROM t;
j
1
2
2
3
3
3
4
...
9
10
10
10
10
10
10
10
10
10
10
> WITH RECURSIVE t(n) AS (
VALUES (1)
UNION ALL
SELECT n+1 FROM t
)
SELECT * FROM t LIMIT 10;
n
1
2
3
4
5
6
7
8
9
10
> CREATE TABLE department (
id INTEGER,
parent_department INTEGER,
name string
);
> INSERT INTO department VALUES
(0, NULL, 'ROOT'),
(1, 0, 'A'),
(2, 1, 'B'),
(3, 2, 'C'),
(4, 2, 'D'),
(5, 0, 'E'),
(6, 4, 'F'),
(7, 5, 'G');
> WITH RECURSIVE subdepartment AS (
SELECT name as root_name, * FROM department WHERE name = 'A'
UNION ALL
SELECT sd.root_name, d.* FROM department AS d, subdepartment AS sd
WHERE d.parent_department = sd.id
)
SELECT * FROM subdepartment ORDER BY name;
root_name id parent_department name
A 1 0 A
A 2 1 B
A 3 2 C
A 4 2 D
A 6 4 F
> WITH RECURSIVE subdepartment(level, id, parent_department, name) AS (
SELECT 1, * FROM department WHERE name = 'A'
UNION ALL
SELECT sd.level + 1, d.* FROM department AS d, subdepartment AS sd
WHERE d.parent_department = sd.id
)
SELECT * FROM subdepartment ORDER BY name;
level id parent_department name
1 1 0 A
2 2 1 B
3 3 2 C
3 4 2 D
4 6 4 F
> WITH RECURSIVE subdepartment(level, id, parent_department, name) AS (
SELECT 1, * FROM department WHERE name = 'A'
UNION ALL
SELECT sd.level + 1, d.* FROM department AS d, subdepartment AS sd
WHERE d.parent_department = sd.id
)
SELECT * FROM subdepartment WHERE level >= 2 ORDER BY name;
level id parent_department name
2 2 1 B
3 3 2 C
3 4 2 D
4 6 4 F
> CREATE TABLE tree(id INTEGER, parent_id INTEGER);
> INSERT INTO tree
VALUES (1, NULL), (2, 1), (3, 1), (4, 2), (5, 2), ( 6, 2), ( 7, 3), ( 8, 3),
(9, 4), (10,4), (11,7), (12,7), (13,7), (14, 9), (15,11), (16,11);
> WITH RECURSIVE t(id, path) AS (
VALUES(1,cast(array() as array<Int>))
UNION ALL
SELECT tree.id, t.path || array(tree.id)
FROM tree JOIN t ON (tree.parent_id = t.id)
)
SELECT t1.*, t2.*
FROM t AS t1 JOIN t AS t2
ON (t1.path[0] = t2.path[0] AND
size(t1.path) = 1 AND
size(t2.path) > 1)
ORDER BY t1.id, t2.id;
id path id path
2 [2] 4 [2,4]
2 [2] 5 [2,5]
2 [2] 6 [2,6]
2 [2] 9 [2,4,9]
2 [2] 10 [2,4,10]
2 [2] 14 [2,4,9,14]
3 [3] 7 [3,7]
3 [3] 8 [3,8]
3 [3] 11 [3,7,11]
3 [3] 12 [3,7,12]
3 [3] 13 [3,7,13]
3 [3] 15 [3,7,11,15]
3 [3] 16 [3,7,11,16]
> CREATE TABLE tree(id INTEGER, parent_id INTEGER);
> INSERT INTO tree
VALUES (1, NULL), ( 2,1), (3,1 ), ( 4,2), ( 5,2), ( 6,2), ( 7, 3), ( 8, 3),
(9, 4), (10,4), (11,7), (12,7), (13,7), (14,9), (15,11), (16,11);
> WITH RECURSIVE t(id, path) AS (
VALUES(1,cast(array() as array<Int>))
UNION ALL
SELECT tree.id, t.path || array(tree.id)
FROM tree JOIN t ON (tree.parent_id = t.id)
)
SELECT t1.id, count(*)
FROM t AS t1 JOIN t AS t2
ON (t1.path[0] = t2.path[0] AND
size(t1.path) = 1 AND
size(t2.path) > 1)
GROUP BY t1.id
ORDER BY t1.id;
id count(1)
2 6
3 7
> CREATE TABLE tree(id INTEGER, parent_id INTEGER);
> INSERT INTO tree
VALUES (1, NULL), ( 2,1), ( 3,1), ( 4,2), ( 5,2), ( 6,2), ( 7, 3), ( 8, 3),
(9, 4), (10,4), (11,7), (12,7), (13,7), (14,9), (15,11), (16,11);
> WITH RECURSIVE t(id, path) AS (
VALUES(1,cast(array() as array<Int>))
UNION ALL
SELECT tree.id, t.path || array(tree.id)
FROM tree JOIN t ON (tree.parent_id = t.id)
)
SELECT id, path FROM t
ORDER BY id;
id path
1 []
2 [2]
3 [3]
4 [2,4]
5 [2,5]
6 [2,6]
7 [3,7]
8 [3,8]
9 [2,4,9]
10 [2,4,10]
11 [3,7,11]
12 [3,7,12]
13 [3,7,13]
14 [2,4,9,14]
15 [3,7,11,15]
16 [3,7,11,16]
> WITH RECURSIVE generate_series(gs) AS (
SELECT 1
UNION ALL
SELECT gs+1 FROM generate_series WHERE gs < 9
),
x( s, ind ) AS
( SELECT sud, position( ' ' IN sud )
FROM (SELECT '4 8725 5 64 213 29 8 6 73 1 8 2 4 97 15 2 3 2 1 659 28 2 37946'::STRING
AS sud) xx
UNION ALL
SELECT substr( s, 1, ind - 1 ) || z || substr( s, ind + 1 ),
position(' ' in repeat('x',ind) || substr( s, ind + 1 ) )
FROM x,
(SELECT gs::STRING AS z FROM generate_series) z
WHERE ind > 0
AND NOT EXISTS ( SELECT *
FROM generate_series
WHERE z.z = substr( s, ( (ind - 1 ) DIV 9 ) * 9 + gs, 1 )
OR z.z = substr( s, mod( ind - 1, 9 ) - 8 + gs * 9, 1 )
OR z.z = substr( s, mod( ( ( ind - 1 ) DIV 3 ), 3 ) * 3
+ ( ( ind - 1 ) DIV 27 ) * 27 + gs
+ ( ( gs - 1 ) DIV 3 ) * 6, 1 )
)
)
SELECT s FROM x WHERE ind = 0;
431872569587649213629351874245968731168723495973415628394286157716594382852137946
431872569587649213629351874245968731168723495973415628394286157716594382852137946
631872594587649213429351867245968731168723459973415628394286175716594382852137946
> WITH RECURSIVE t1(n) AS (
VALUES(2)
UNION ALL
SELECT n+1 FROM t1 WHERE n < 100
),
t2 (n, i) AS (
SELECT 2*n, 2
FROM t1 WHERE 2*n <= 150
UNION ALL
(
WITH t3(k) AS (
SELECT max(i) OVER () + 1 FROM t2
),
t4(k) AS (
SELECT DISTINCT k FROM t3
)
SELECT k*n, k
FROM t1 CROSS JOIN t4
WHERE k*k <= 150
)
)
SELECT n FROM t1 EXCEPT
SELECT n FROM t2
ORDER BY 1;
2
3
5
7
11
13
17
19
...
> WITH RECURSIVE t(n) MAX RECURSION LEVEL 10 AS (
VALUES(1)
UNION ALL
SELECT n+1 FROM t WHERE n < 100
)
SELECT * FROM t;
Error: RECURSION_LEVEL_LIMIT_EXCEEDED
> WITH RECURSIVE t(n) AS (
VALUES(1)
UNION ALL
SELECT n+1 FROM t
)
SELECT * FROM t LIMIT 10;
n
1
2
3
4
5
6
7
8
9
10
> WITH RECURSIVE t(n) AS (
VALUES(1)
UNION ALL
SELECT n+1 FROM t
)
SELECT * FROM t ORDER BY n LIMIT 10;
Error: RECURSION_LEVEL_LIMIT_EXCEEDED
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4