mirror of
https://github.com/tursodatabase/libsql.git
synced 2024-12-12 15:33:44 +00:00
112 lines
2.6 KiB
Plaintext
112 lines
2.6 KiB
Plaintext
|
|
WITH RECURSIVE
|
|
xyz(n) AS (
|
|
SELECT upper((SELECT x FROM (
|
|
SELECT x,y,z FROM t2
|
|
INTERSECT SELECT a,b,c FROM t3
|
|
EXCEPT SELECT c,b,a FROM t1
|
|
UNION SELECT a,b,c FROM t3
|
|
INTERSECT SELECT a,b,c FROM t3
|
|
EXCEPT SELECT c,b,a FROM t1
|
|
UNION SELECT a,b,c FROM t3
|
|
ORDER BY y COLLATE NOCASE DESC,x,z)))
|
|
UNION ALL
|
|
SELECT n || '+' FROM xyz WHERE length(n)<5
|
|
)
|
|
SELECT n FROM xyz ORDER BY +n;
|
|
|
|
|
|
CREATE TABLE t4(a, b);
|
|
CREATE TABLE t5(c, d);
|
|
|
|
INSERT INTO t5 VALUES(1, 'x');
|
|
INSERT INTO t5 VALUES(2, 'x');
|
|
INSERT INTO t4 VALUES(3, 'x');
|
|
INSERT INTO t4 VALUES(4, 'x');
|
|
|
|
CREATE INDEX i1 ON t4(a);
|
|
CREATE INDEX i2 ON t5(c);
|
|
|
|
|
|
SELECT c, d FROM t5
|
|
UNION ALL
|
|
SELECT a, b FROM t4 WHERE f()==f()
|
|
ORDER BY 1,2
|
|
|
|
|
|
CREATE TABLE t6(a, b);
|
|
CREATE TABLE t7(c, d);
|
|
|
|
INSERT INTO t7 VALUES(2, 9);
|
|
INSERT INTO t6 VALUES(3, 0);
|
|
INSERT INTO t6 VALUES(4, 1);
|
|
INSERT INTO t7 VALUES(5, 6);
|
|
INSERT INTO t6 VALUES(6, 0);
|
|
INSERT INTO t7 VALUES(7, 6);
|
|
|
|
CREATE INDEX i6 ON t6(a);
|
|
CREATE INDEX i7 ON t7(c);
|
|
|
|
|
|
SELECT c, f(d,c,d,c,d) FROM t7
|
|
UNION ALL
|
|
SELECT a, b FROM t6
|
|
ORDER BY 1,2
|
|
|
|
|
|
CREATE TABLE t8(a, b);
|
|
CREATE TABLE t9(c, d);
|
|
|
|
|
|
DROP TABLE IF EXISTS t1;
|
|
DROP TABLE IF EXISTS t2;
|
|
CREATE TABLE t1(a INTEGER);
|
|
CREATE TABLE t2(b TEXT);
|
|
INSERT INTO t2(b) VALUES('12345');
|
|
SELECT * FROM (SELECT a FROM t1 UNION SELECT b FROM t2) WHERE a=a;
|
|
|
|
|
|
CREATE TABLE t1(c1); INSERT INTO t1 VALUES(12),(123),(1234),(NULL),('abc');
|
|
CREATE TABLE t2(c2); INSERT INTO t2 VALUES(44),(55),(123);
|
|
CREATE TABLE t3(c3,c4); INSERT INTO t3 VALUES(66,1),(123,2),(77,3);
|
|
CREATE VIEW t4 AS SELECT c3 FROM t3;
|
|
CREATE VIEW t5 AS SELECT c3 FROM t3 ORDER BY c4;
|
|
|
|
|
|
SELECT * FROM t1, t2 WHERE c1=(SELECT 123 INTERSECT SELECT c2 FROM t4) AND c1=123;
|
|
|
|
|
|
SELECT * FROM t1, t2 WHERE c1=(SELECT 123 INTERSECT SELECT c2 FROM t5) AND c1=123;
|
|
|
|
|
|
CREATE TABLE a(b);
|
|
CREATE VIEW c(d) AS SELECT b FROM a ORDER BY b;
|
|
SELECT sum(d) OVER( PARTITION BY(SELECT 0 FROM c JOIN a WHERE b =(SELECT b INTERSECT SELECT d FROM c) AND b = 123)) FROM c;
|
|
|
|
|
|
CREATE TABLE x1(x);
|
|
CREATE TABLE t1(a, b, c, d);
|
|
CREATE INDEX t1a ON t1(a);
|
|
CREATE INDEX t1b ON t1(b);
|
|
|
|
|
|
SELECT 'ABCD' FROM t1
|
|
WHERE (a=? OR b=?)
|
|
AND (0 OR (SELECT 'xyz' INTERSECT SELECT a ORDER BY 1))
|
|
|
|
|
|
CREATE TABLE t1(a COLLATE nocase);
|
|
CREATE TABLE t2(b COLLATE nocase);
|
|
|
|
INSERT INTO t1 VALUES('ABC');
|
|
INSERT INTO t2 VALUES('abc');
|
|
|
|
|
|
SELECT a FROM t1 INTERSECT SELECT b FROM t2;
|
|
|
|
|
|
SELECT * FROM (
|
|
SELECT a FROM t1 INTERSECT SELECT b FROM t2
|
|
) WHERE a||'' = 'ABC';
|
|
|