OceanBase 集合

數(shù)據(jù)庫中的集合操作可以把多個查詢的結(jié)果組合成一個結(jié)果集。集合操作主要包含：

• UNION

• INTERSECT

• EXCEPT/MINUS

這里需要注意的是參加集合操作的各查詢結(jié)果的列數(shù)必須相同，對應(yīng)的數(shù)據(jù)類型也必須兼容。對于 UNION 來說用戶可以指定 UNION的屬性為 ALL 和 DISTINCT/UNIQUE。分別代表集合可重復(fù)，和集合不可重復(fù)。而其它幾種集合操作是不能指定 ALL 屬性的（它們只有 DISTINCT 屬性）。所有的集合操作默認(rèn)的屬性是 DISTINCT。在 Oceanbase 數(shù)據(jù)庫中，集合操作中可以指定 ORDER BY 和 LIMIT 子句，但是不允許其他子句的出現(xiàn)，如下所示：

obclient> create table t1(a int primary key, b int, c int);
Query OK, 0 rows affected (0.16 sec)
obclient> create table t2(a int primary key, b int, c int);
Query OK, 0 rows affected (0.10 sec)

--支持 union 語句中出現(xiàn) order by 和 limit 子句
obclient> (select * from t1 union all select * from t2) order by a limit 10;
Empty set (0.02 sec)

--不支持 union 語句中出現(xiàn)除 order by 和 limit 子句的其他子句，比如 group by
obclient> (select * from t1 union all select * from t2) group by a limit 10;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near 'group by a limit 10' at line 1

UNION 例子

該例子獲取 t1 和 t2 中所有不重復(fù)的行。

obclient> create table t1(a int, b int, c int);
Query OK, 0 rows affected (0.12 sec)
obclient> create table t2(a int, b int, c int);
Query OK, 0 rows affected (0.11 sec)
obclient> insert into t1 values (1,1,1),(2,2,2),(3,3,3);
Query OK, 3 rows affected (0.07 sec)
Records: 3  Duplicates: 0  Warnings: 0
obclient> insert into t2 values (2,2,2),(3,3,3),(4,4,4);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0
obclient> select * from t1 union select * from t2;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 |    1 |    1 |
|    2 |    2 |    2 |
|    3 |    3 |    3 |
|    4 |    4 |    4 |
+------+------+------+
4 rows in set (0.01 sec)

UNION All 例子

該例子獲取 t1 和 t2 中的所有行，不進(jìn)行去重。

obclient> create table t1(a int, b int, c int);
Query OK, 0 rows affected (0.12 sec)
obclient> create table t2(a int, b int, c int);
Query OK, 0 rows affected (0.11 sec)
obclient> insert into t1 values (1,1,1),(2,2,2),(3,3,3);
Query OK, 3 rows affected (0.07 sec)
Records: 3  Duplicates: 0  Warnings: 0
obclient> insert into t1 values (2,2,2),(3,3,3),(4,4,4);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0
obclient> select * from t1 union all select * from t2;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 |    1 |    1 |
|    2 |    2 |    2 |
|    3 |    3 |    3 |
|    2 |    2 |    2 |
|    3 |    3 |    3 |
|    4 |    4 |    4 |
+------+------+------+
6 rows in set (0.02 sec)

INTERSECT 例子

該例子獲取同時出現(xiàn)在 t1 和 t2 中的行，并且去重。

obclient> create table t1(a int, b int, c int);
Query OK, 0 rows affected (0.12 sec)
obclient> create table t2(a int, b int, c int);
Query OK, 0 rows affected (0.12 sec)
obclient> insert into t1 values (1,1,1),(2,2,2),(3,3,3);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0
obclient> insert into t2 values (2,2,2),(3,3,3),(3,3,3),(4,4,4);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0
obclient> select * from t1 intersect select * from t2;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    2 |    2 |    2 |
|    3 |    3 |    3 |
+------+------+------+
2 rows in set (0.01 sec)

EXCEPT/MINUS 例子

該例子獲取出現(xiàn)在 t1 中，但是不出現(xiàn)在 t2 中的行，并且去重。

obclient> create table t1(a int, b int, c int);
Query OK, 0 rows affected (0.12 sec)
obclient> create table t2(a int, b int, c int);
Query OK, 0 rows affected (0.12 sec)
obclient> insert into t1 values (1,1,1),(2,2,2),(3,3,3);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0
obclient> insert into t2 values (2,2,2),(3,3,3),(3,3,3),(4,4,4);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0
obclient> select * from t1 except select * from t2;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 |    1 |    1 |
+------+------+------+
1 row in set (0.02 sec)