DML - SELECT - 《OceanBase v3.1.1 官方教程》

带有 with clause 的 SELECT

该语句用于查询表中的内容。

格式

参数解释

示例

以如下表a为例。

CREATE TABLE a (id INT,name VARCHAR(10),num INT);
INSERT INTO a VALUES (1, 'a',100);
INSERT INTO a VALUES (2, 'b',200);
INSERT INTO a VALUES (3, 'a',50);

从表a中读取name数据。

obclient> SELECT name FROM a; 
+------+
| NAME |
+------+
| a    |
| b    |
| a    |
+------+
3 rows in set (0.00 sec)

在查询结果中对name进行去重处理。

obclient> SELECT DISTINCT name FROM a;
+------+
| NAME |
+------+
| a    |
| b    |
+------+
2 rows in set (0.00 sec)

从表a中查询 id、name和num，然后把num列除以 2 输出，输出的列名为avg。

obclient> SELECT id, name, num/2 AS avg FROM a;
+----+------+------+
| ID | NAME | AVG  |
+----+------+------+
|  1 | a    |   50 |
|  2 | b    |  100 |
|  3 | a    |   25 |
+----+------+------+
3 rows in set (0.00 sec)

从表a中根据筛选条件“ name = ‘a’ ”，输出对应的id 、name和num 。

obclient> SELECT id, name, num FROM a WHERE name = 'a';
+----+------+------+
| ID | NAME | NUM  |
+----+------+------+
|  1 | a    |  100 |
|  3 | a    |   50 |
+----+------+------+
2 rows in set (0.00 sec)

从表a中查询name，按照name分组对num求和，并输出。

obclient> SELECT id, name, num FROM a WHERE name = 'a';
+----+------+------+
| ID | NAME | NUM  |
|  1 | a    |  100 |
|  3 | a    |   50 |
+----+------+------+

从表a中查询id、name和num，根据num按升序（ASC）输出查询结果。

obclient> SELECT * FROM a ORDER BY num ASC;
+----+------+------+
| ID | NAME | NUM  |
+----+------+------+
|  3 | a    |   50 |
|  1 | a    |  100 |
|  2 | b    |  200 |
+----+------+------+
3 rows in set (0.00 sec)

从表a中查询id、name和num，根据num按降序（DESC）输出查询结果。

obclient> SELECT * FROM a ORDER BY num DESC;
+----+------+------+
| ID | NAME | NUM  |
+----+------+------+
|  2 | b    |  200 |
|  1 | a    |  100 |
|  3 | a    |   50 |
+----+------+------+
3 rows in set (0.00 sec)

从表a中查询指定id的行，并使用FOR UPDATE子句把查询结果行进行锁定。

/* 在会话 1 中查询表 a 中 id=1 的行并锁定 */
obclient> SELECT * FROM a WHERE id=1 FOR UPDATE;
+------+------+------+
| ID   | NAME | NUM  |
+------+------+------+
|    1 | a    |  100 |
+------+------+------+
1 row in set (0.01 sec)
/* 在会话 2 中查询表 a 中 id=1 或 id=2 的行并锁定 */
obclient> SELECT * FROM a WHERE id=1 or id=2 FOR UPDATE;
ORA-30006: resource busy; acquire with WAIT timeout expired
obclient> SELECT * FROM a WHERE id=1 or id=2 FOR UPDATE SKIP LOCKED;
+------+------+------+
| ID   | NAME | NUM  |
+------+------+------+
|    2 | b    |  200 |
+------+------+------+
1 row in set (0.01 sec)

从表a中按 name 和 num 分组查询并统计每个分组中的数量。

obclient> SELECT name, num, COUNT(*) from a GROUP BY GROUPING SETS(name, num);
+------+------+----------+
| NAME | NUM  | COUNT(*) |
+------+------+----------+
| a    | NULL |        2 |
| b    | NULL |        1 |
| NULL |  100 |        1 |
| NULL |  200 |        1 |
| NULL |   50 |        1 |
+------+------+----------+
5 rows in set (0.01 sec)

该语句用于对多个SELECT查询的结果进行UNION、MINUS、INTERSECT。

格式

select_clause_set:
  simple_select [ UNION | UNION ALL |  | INTERSECT] select_clause_set_right
  [ORDER BY sort_list_columns]
select_clause_set_right:
  simple_select |

参数解释

参数	描述
UNION ALL	合并两个查询的结果
UNION	合并两个查询的结果，并去重
MINUS	从左查询结果集中去重出现在右查询中的结果，并去重
INTERSECT	保留左查询结果集中出现在右查询中的结果，并去重

示例

以如下两表的数据为例：

CREATE TABLE t1 (c1 INT, c2 INT);
CREATE TABLE t2 (c1 INT, c2 INT);
INSERT INTO t2 VALUES (1, 1), (2, -2), (3, 3);

计算t1、t2的所有的记录

计算t1、t2的去重后的所有记录

obclient>SELECT c1, c2 FROM t1 UNION SELECT c1, c2 FROM t2;
+------+------+
| C1   | C2   |
+------+------+
|    1 |   -1 |
|    2 |   -2 |
|    1 |    1 |
|    3 |    3 |
+------+------+
4 rows in set (0.01 sec)

计算t1和t2的交集

SELECT c1, c2 FROM t1 INTERSECT SELECT c1, c2 FROM t2;
+------+------+
| C1   | C2   |
+------+------+
|    2 |   -2 |
+------+------+

计算t1和t2的差集

obclient>SELECT c1, c2 FROM t1 INTERSECT SELECT c1, c2 FROM t2;
+------+------+
| C1   | C2   |
+------+------+
|    2 |   -2 |
+------+------+

如果查询语句中有多个相同的子查询，可以把相同的子查询放在with clause作为公共表达式，在主体查询中直接引用即可。

格式

with_clause_select:
    with_clause simple_select
with_clause:
    WITH table_name [opt_column_alias_name_list] AS ( select_clause )
select_clause:
    simple_select | select_clause_set
opt_column_alias_name_list:
    (column_name_list)
column_name_list:
    column_name | column_name , column_name_list

参数解释

无

示例

CREATE TABLE t1(c1 INT, c2 INT, c3 INT);
CREATE TABLE t2(c1 INT);
INSERT INTO t1 VALUES(1,1,1);
INSERT INTO t1 VALUES(2,2,2);
INSERT INTO t1 VALUES(3,3,3);
INSERT INTO t2 VALUES(4);
obclient>SELECT * FROM t1 WHERE c1 > (SELECT COUNT(*) FROM t2)
                      AND c2 > (SELECT COUNT(*) FROM t2)
                      AND c3 > (SELECT COUNT(*) FROM t2);
+------+------+------+
| C1   | C2   | C3   |
+------+------+------+
|    2 |    2 |    2 |
|    3 |    3 |    3 |
+------+------+------+
2 rows in set (0.01 sec)

可以抽取相同子查询为with clause：

obclient>WITH TEMP(cnt) AS (SELECT COUNT(*) FROM t2)
      SELECT t1.* FROM t1, temp WHERE c1 > temp.cnt AND c2 > temp.cnt 
      AND c3 > temp.cnt;
+------+------+------+
| C1   | C2   | C3   |
+------+------+------+
|    2 |    2 |    2 |
|    3 |    3 |    3 |
+------+------+------+