SQLite3神奇的UNION、UNION ALL与LIMIT组合

Martin89

于 2022-09-21 17:52:06 发布

阅读量831

点赞数

分类专栏： database 文章标签：数据库 sqlite sql

原文链接：http://ddrv.cn/a/566441

版权

database 专栏收录该内容

16 篇文章 0 订阅

订阅专栏

原文链接：http://ddrv.cn/a/566441
xxxxxx@ubuntu:~/sqlite/SQLite-036ebf72_orig_3.18.2$ ./sqlite3 t.db
SQLite version 3.18.2 2017-06-17 09:59:36
Enter ".help" for usage hints.
sqlite> create table t1(id integer primary key autoincrement, data text);
sqlite> create table t2(id integer primary key autoincrement, data text);
sqlite> insert into t1(data) values('t1_d1');
sqlite> insert into t1(data) values('t1_d2');
sqlite> insert into t2(data) values('t2_d1');
sqlite> insert into t2(data) values('t2_d2');
sqlite> select * from t1 union select * from t2;
1|t1_d1
1|t2_d1
2|t1_d2
2|t2_d2
sqlite> select * from(select * from(select * from t1 limit 1) union select * from(select * from t2 limit 1));
1|t1_d1
1|t2_d1
sqlite> select * from(select * from(select * from t1 limit 1) union all select * from(select * from t2 limit 1));
1|t1_d1
sqlite>
sqlite>
sqlite> select * from(select * from(select * from t1 limit 2) union all select * from(select * from t2 limit 2));
1|t1_d1
2|t1_d2
sqlite> select * from(select * from(select * from t1) union all select * from(select * from t2 limit 2));
1|t1_d1
2|t1_d2
1|t2_d1
2|t2_d2
sqlite> select * from(select * from(select * from t1 limit 2) union all select * from(select * from t2));
1|t1_d1
2|t1_d2
sqlite> select * from(select * from(select * from t1 limit 2) union select * from(select * from t2 limit 2));
1|t1_d1
1|t2_d1
2|t1_d2
2|t2_d2
sqlite> select * from(select * from(select * from t1) union select * from(select * from t2 limit 2));
1|t1_d1
1|t2_d1
2|t1_d2
2|t2_d2
sqlite> select * from(select * from(select * from t1 limit 2) union select * from(select * from t2));
1|t1_d1
1|t2_d1
2|t1_d2
2|t2_d2
sqlite>