单表查询

本文将向您介绍如何使用 SQL 语句进行 OceanBase 数据库中的单表查询操作。

前提条件

您已连接 OceanBase 数据库 Oracle 模式租户。更多有关连接数据库的操作信息，请参见连接方式概述。
您已拥有 SELECT 权限。更多查看当前用户权限的相关操作信息，请参见查看用户权限。如果不具备该权限，请联系管理员为您授权，有关用户授权的相关操作信息，请参见直接授予权限。

语法

请使用 SELECT 语句进行查询数据。

SELECT 语句单表查询的一般结构如下：

SELECT [ALL | DISTINCT] select_list FROM table_name
              [ WHERE query_condition ]
              [ GROUP BY group_by_expression ]
              [ HAVING group_condition ]
              [ ORDER BY column_list ][ASC | DESC]
              [ Row_Limiting_Clause ]

column_list：
 column_name[,column_name...]

参数解释：

参数	说明
select_list	要检索的列的列表，可以是列名、表达式、聚合函数等。可以使用逗号分隔多个列。
table_name	要检索数据的表的名称。
WHERE query_condition	可选参数，用于指定检索的条件。只有符合条件的行才会被返回。
GROUP BY group_by_condition	可选参数，用于按照指定的列对结果进行分组。通常与聚合函数一起使用。
HAVING group_condition	可选参数，用于过滤分组后的结果集，只返回满足条件的分组。
ORDER BY column_list	可选参数，用于对结果集进行排序。可以指定一个或多个列进行排序。
ASC	DESC	可选参数，用于指定排序的顺序。ASC 表示升序（默认），DESC 表示降序。
LIMIT limit_clause	可选参数，用于限制查询返回的行，实现分页查询功能。可以指定偏移量，以及要返回的行数或行百分比。可以结合 ORDER BY 子句以确保排序顺序，从而获得一致的结果。
column_list	用于指定要检索的列的参数，可以是单个列或多个列，用逗号分隔。
column_name	要检索的列的名称。

SELECT 关键字执行顺序简介

当 WHERE、GROUP BY、HAVING、ORDER BY 这些关键字一起使用时，先后顺序有明确的限制。关键字执行顺序如下：

执行 FROM 找到表。
执行 WHERE 指定约束条件。
执行 GROUP BY 将取出的每条记录进行分组。如果没有 GROUP BY，则整体作为一组。
执行 HAVING 将分组的结果进行筛选。
执行 SELECT。
执行 DISTINCT 去重。
执行 ORDER BY 将结果按条件升序或降序排序。
执行 ROWNUM 限制结果的显示条数。

注意

WHERE 和 HAVING 的区别是，WHERE 是在分组前对数据进行筛选，而 HAVING 是在分组后的结果里进行筛选，最后返回整个 SQL 的查询结果。

创建测试表并添加测试数据

创建表 dept。

CREATE TABLE dept(  
    deptno           NUMBER(2,0),  
    dname            VARCHAR(14),  
    location         VARCHAR(13),   
    CONSTRAINT pk_dept PRIMARY KEY(deptno)  
);

向表 dept 中插入 6 条数据。

INSERT INTO dept VALUES
(20,'Finance','beijing'),
(35,'Administration','hangzhou'),
(40,'Development','xian'),    
(30,'Workshop','guangzhou'),    
(25,'Legal affairs','shanghai'),    
(45,'Office','suzhou');

创建表 emp。

CREATE TABLE emp(  
    empno         NUMBER(4,0),  
    empname       VARCHAR(10),  
    job           VARCHAR(9),  
    mgr           NUMBER(4,0),  
    hiredate      DATE,  
    sal           NUMBER(7,2),  
    comm          NUMBER(7,2),        
    deptno        NUMBER(2,0),
    age           NUMBER(2,0),
    CONSTRAINT PK_emp PRIMARY KEY (empno),
    CONSTRAINT FK_deptno  FOREIGN KEY (deptno)  REFERENCES dept (deptno)  
);

向表 emp 中插入 6 条数据。

INSERT INTO emp VALUES
(1369,'SMITH','CLERK',1902,DATE'1980-12-17',800.00,NULL,20,22),
(1499,'ALLEN','SALESMAN',1698,DATE'1981-02-20',1600.00,300.00,35,22),
(1566,'JONES','MANAGER',1839,DATE'1981-04-02',2975.00, NULL,40,22),    
(1698,'BLAKE' ,'MANAGER',1839,DATE'1981-05-01',2850.00,NULL ,30,33),    
(1788,'SCOTT','ANALYST',1566,DATE'1987-07-15',3000.00,NULL ,25,33),    
(1902,'FORD','ANALYST',1566,DATE'1981-12-05',3000.00, NULL,45,22);

基本查询

查询全部列

* 表示返回表中所有字段。示例如下：

obclient [SYS]> SELECT * FROM emp;

返回结果如下：

+-------+---------+----------+------+-----------+------+------+--------+------+
| EMPNO | EMPNAME | JOB      | MGR  | HIREDATE  | SAL  | COMM | DEPTNO | AGE  |
+-------+---------+----------+------+-----------+------+------+--------+------+
|  1369 | SMITH   | CLERK    | 1902 | 17-DEC-80 |  800 | NULL |     20 |   22 |
|  1499 | ALLEN   | SALESMAN | 1698 | 20-FEB-81 | 1600 |  300 |     35 |   22 |
|  1566 | JONES   | MANAGER  | 1839 | 02-APR-81 | 2975 | NULL |     40 |   22 |
|  1698 | BLAKE   | MANAGER  | 1839 | 01-MAY-81 | 2850 | NULL |     30 |   33 |
|  1788 | SCOTT   | ANALYST  | 1566 | 15-JUL-87 | 3000 | NULL |     25 |   33 |
|  1902 | FORD    | ANALYST  | 1566 | 05-DEC-81 | 3000 | NULL |     45 |   22 |
+-------+---------+----------+------+-----------+------+------+--------+------+
6 rows in set

等价于：

obclient [SYS]> SELECT empname,empno,job,mgr,hiredate,sal,comm,deptno FROM emp;

返回结果如下：

+---------+-------+----------+------+-----------+------+------+--------+
| EMPNAME | EMPNO | JOB      | MGR  | HIREDATE  | SAL  | COMM | DEPTNO |
+---------+-------+----------+------+-----------+------+------+--------+
| SMITH   |  1369 | CLERK    | 1902 | 17-DEC-80 |  800 | NULL |     20 |
| ALLEN   |  1499 | SALESMAN | 1698 | 20-FEB-81 | 1600 |  300 |     35 |
| JONES   |  1566 | MANAGER  | 1839 | 02-APR-81 | 2975 | NULL |     40 |
| BLAKE   |  1698 | MANAGER  | 1839 | 01-MAY-81 | 2850 | NULL |     30 |
| SCOTT   |  1788 | ANALYST  | 1566 | 15-JUL-87 | 3000 | NULL |     25 |
| FORD    |  1902 | ANALYST  | 1566 | 05-DEC-81 | 3000 | NULL |     45 |
+---------+-------+----------+------+-----------+------+------+--------+
6 rows in set

查询时指定表别名

查询表 emp 的数据并指定表别名为 t。

obclient [SYS]> SELECT t.* FROM (emp) t;

返回结果如下：

+-------+---------+----------+------+-----------+------+------+--------+------+
| EMPNO | EMPNAME | JOB      | MGR  | HIREDATE  | SAL  | COMM | DEPTNO | AGE  |
+-------+---------+----------+------+-----------+------+------+--------+------+
|  1369 | SMITH   | CLERK    | 1902 | 17-DEC-80 |  800 | NULL |     20 |   22 |
|  1499 | ALLEN   | SALESMAN | 1698 | 20-FEB-81 | 1600 |  300 |     35 |   22 |
|  1566 | JONES   | MANAGER  | 1839 | 02-APR-81 | 2975 | NULL |     40 |   22 |
|  1698 | BLAKE   | MANAGER  | 1839 | 01-MAY-81 | 2850 | NULL |     30 |   33 |
|  1788 | SCOTT   | ANALYST  | 1566 | 15-JUL-87 | 3000 | NULL |     25 |   33 |
|  1902 | FORD    | ANALYST  | 1566 | 05-DEC-81 | 3000 | NULL |     45 |   22 |
+-------+---------+----------+------+-----------+------+------+--------+------+
6 rows in set

查询指定列

obclient [SYS]> SELECT empname,deptno FROM emp;

返回结果如下：

+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| SMITH   |     20 |
| ALLEN   |     35 |
| JONES   |     40 |
| BLAKE   |     30 |
| SCOTT   |     25 |
| FORD    |     45 |
+---------+--------+
6 rows in set

在查询的同时为列设置别名

obclient [SYS]> SELECT empname AS 员工姓名, deptno AS 部门编号 FROM emp;

返回结果如下：

+--------------+--------------+
| 员工姓名     | 部门编号     |
+--------------+--------------+
| SMITH        |           20 |
| ALLEN        |           35 |
| JONES        |           40 |
| BLAKE        |           30 |
| SCOTT        |           25 |
| FORD         |           45 |
+--------------+--------------+
6 rows in set

在查询时消除取值重复的行

obclient [SYS]> SELECT DISTINCT age FROM emp;

返回结果如下：

+------+
| AGE  |
+------+
|   22 |
|   33 |
+------+
2 rows in set

在查询时可以使用 `ROWNUM` 用来限制返回的行数

obclient [SYS]> SELECT empname, deptno FROM emp WHERE ROWNUM <= 3;

返回结果如下：

+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| SMITH   |     20 |
| ALLEN   |     35 |
| JONES   |     40 |
+---------+--------+
3 rows in set

查询经过计算的值

obclient [SYS]> SELECT empname, sal-100,job FROM emp;

返回结果如下：

+---------+---------+----------+
| EMPNAME | SAL-100 | JOB      |
+---------+---------+----------+
| SMITH   |     700 | CLERK    |
| ALLEN   |    1500 | SALESMAN |
| JONES   |    2875 | MANAGER  |
| BLAKE   |    2750 | MANAGER  |
| SCOTT   |    2900 | ANALYST  |
| FORD    |    2900 | ANALYST  |
+---------+---------+----------+
6 rows in set

在查询中可以为指定列使用函数，如下示例中要求用小写字母表示 job。详细信息请参见在查询中使用操作符和函数。

obclient [SYS]> SELECT empname, sal-100, LOWER(job) FROM emp;

返回结果如下：

+---------+---------+------------+
| EMPNAME | SAL-100 | LOWER(JOB) |
+---------+---------+------------+
| SMITH   |     700 | clerk      |
| ALLEN   |    1500 | salesman   |
| JONES   |    2875 | manager    |
| BLAKE   |    2750 | manager    |
| SCOTT   |    2900 | analyst    |
| FORD    |    2900 | analyst    |
+---------+---------+------------+
6 rows in set

FROM 函数查询

在 OceanBase 数据库 Oracle 模式中，可以使用 SELECT * FROM FUNCTION(*); 的语法调用返回表格类型的函数，并将函数返回结果作为表格数据进行查询。

在使用 SELECT * FROM FUNCTION(*); 时，需要注意以下事项：

函数返回的数据类型必须是表格类型。
表格函数返回的列数和列类型必须和被调用方所需要的列数和列类型一一对应。
表格函数返回的行数必须大于等于被调用方所需要的行数。
函数的参数数量和类型必须和函数定义一致。

更多自定义类型的信息，请参见 CREATE TYPE 和用户自定义子类型。

更多创建函数的信息，请参见 CREATE FUNCTION 和创建函数。

示例如下：

定义表格类型 emp_type。

obclient [SYS]> DELIMITER //
obclient [SYS]> CREATE OR REPLACE TYPE emp_type AS OBJECT (id NUMBER); //
Query OK, 0 rows affected

定义包含表格类型的集合类型 emp_type_list。

obclient [SYS]> CREATE OR REPLACE TYPE emp_type_list IS TABLE OF emp_type; //
Query OK, 0 rows affected

创建一个返回表格类型的函数 get_emp_info。

obclient [SYS]> CREATE OR REPLACE FUNCTION get_emp_info
        RETURN emp_type_list PIPELINED
    IS CURSOR emp_list_cursor IS SELECT EMPNO FROM emp;
        v_emp_id_type emp_type;
      v_emp_id varchar2(5);
      BEGIN
      OPEN emp_list_cursor;
      LOOP
      FETCH emp_list_cursor INTO v_emp_id;
      EXIT WHEN emp_list_cursor%notfound;
      v_emp_id_type := emp_type(v_emp_id);
      PIPE ROW(v_emp_id_type);
      END LOOP;
      CLOSE emp_list_cursor;
      RETURN;
      END;//
Query OK, 0 rows affected

obclient [SYS]> DELIMITER ;

查询函数的返回结果。

obclient [SYS]> SELECT * FROM get_emp_info();

返回结果如下：

+------+
| ID   |
+------+
| 1369 |
| 1499 |
| 1566 |
| 1698 |
| 1788 |
| 1902 |
+------+
6 rows in set

数据过滤

要查询满足特定条件的数据时，可以通过在 SELECT 查询语句中添加一个 WHERE 子句来进行数据过滤。WHERE 子句后面可以包含一个或多个条件，这些条件用于筛选数据，只有满足 WHERE 条件的数据才会被返回。可以根据特定需求，通过灵活运用查询条件来过滤和检索目标数据。

在使用 WHERE 子句时，要确保条件正确和使用合适的运算符。

WHERE 子句常用的查询条件如下表所示。

查询条件类型	谓词
比较查询	=，>，<，>=，<=，!=，<>
逻辑查询（多重条件）	AND，OR，NOT
模糊查询（字符匹配）	LIKE，NOT LIKE
区间查询（确定范围）	BETWEEN AND，NOT BETWEEN AND
指定集合查询	IN，NOT IN
NULL 值查询	IS NULL，IS NOT NULL

比较条件查询

等于（=）

查询出指定列中和目标相等的数据。如果值是字符串类型，需要用单引号或者双引号括起来。

obclient [SYS]> SELECT empname, deptno FROM emp WHERE deptno = 30;

返回结果如下：

+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| BLAKE   |     30 |
+---------+--------+
1 row in set

obclient [SYS]> SELECT empname, deptno FROM emp WHERE empname = 'ALLEN';

返回结果如下：

+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| ALLEN   |     35 |
+---------+--------+
1 row in set

不等于（<> 和 !=）

不等于包括 <> 和 != 两种写法。一般格式为：

SELECT column_name [,column_name...] FROM table_name WHERE column_name <> const_value;
SELECT column_name [,column_name...] FROM table_name WHERE column_name != const_value;

示例如下：

obclient> SELECT empname, deptno FROM emp WHERE deptno <> 30;

返回结果如下：

+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| SMITH   |     20 |
| ALLEN   |     35 |
| JONES   |     40 |
| SCOTT   |     25 |
| FORD    |     45 |
+---------+--------+
5 rows in set

obclient> SELECT empname, deptno FROM emp WHERE deptno != 30;

返回结果如下：

+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| SMITH   |     20 |
| ALLEN   |     35 |
| JONES   |     40 |
| SCOTT   |     25 |
| FORD    |     45 |
+---------+--------+
5 rows in set

大于（>）和小于（<）

大于（>）和小于（<）将数值按照大小比较。如果比较的是字符，则按照 ASCII 码对应的值进行比较，比较时按照字符对应的位置逐一进行比较。一般格式为：

SELECT column_name [,column_name...] FROM table_name WHERE column_name < const_value;
SELECT column_name [,column_name...] FROM table_name WHERE column_name > const_value;

说明

大于等于（\>=）和小于等于（\<=）与之类似。

示例如下：

obclient> SELECT empname, deptno FROM emp WHERE deptno > 30;

返回结果如下：

+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| ALLEN   |     35 |
| JONES   |     40 |
| FORD    |     45 |
+---------+--------+
3 rows in set

obclient> SELECT empname, deptno FROM emp WHERE deptno >= 30;

返回结果如下：

+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| ALLEN   |     35 |
| JONES   |     40 |
| BLAKE   |     30 |
| FORD    |     45 |
+---------+--------+
4 rows in set

obclient> SELECT empname, deptno FROM emp WHERE deptno < 30;

返回结果如下：

+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| SMITH   |     20 |
| SCOTT   |     25 |
+---------+--------+
2 rows in set

obclient> SELECT empname, deptno FROM emp WHERE deptno <= 30;

返回结果如下：

+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| SMITH   |     20 |
| BLAKE   |     30 |
| SCOTT   |     25 |
+---------+--------+
3 rows in set

逻辑条件查询

逻辑查询运算符并且（AND）和或者（OR）支持多个条件的查询。

并且（AND）

返回同时满足 AND 两个条件的数据。一般格式为：

SELECT column_name [,column_name...] FROM table_name WHERE
query_condition AND query_condition;

示例如下：

obclient> SELECT empname, deptno, sal FROM emp WHERE deptno<=30 AND sal > 1000;

返回结果如下：

+---------+--------+------+
| EMPNAME | DEPTNO | SAL  |
+---------+--------+------+
| BLAKE   |     30 | 2850 |
| SCOTT   |     25 | 3000 |
+---------+--------+------+
2 rows in set

或者（OR）

只要满足其中一个条件的数据都会被返回。一般格式为：

SELECT column_name [,column_name...] FROM table_name WHERE
query_condition OR query_condition;

示例如下：

obclient> SELECT empname, deptno, sal FROM emp WHERE deptno <= 30 OR sal > 1000;

返回结果如下：

+---------+--------+------+
| EMPNAME | DEPTNO | SAL  |
+---------+--------+------+
| SMITH   |     20 |  800 |
| ALLEN   |     35 | 1600 |
| JONES   |     40 | 2975 |
| BLAKE   |     30 | 2850 |
| SCOTT   |     25 | 3000 |
| FORD    |     45 | 3000 |
+---------+--------+------+
6 rows in set

模糊查询（LIKE）

谓词 LIKE 可以用来进行字符串的匹配。一般格式如下：

[NOT] LIKE pattern

语法含义是查找指定的属性列值与 pattern 相匹配的元组。pattern 可以是一个完整的字符串，也可以含有通配符 % 和 _。其中：

下划线 _ 表示匹配任意一个字符。
百分号 % 表示可以匹配值中的零个或多个字符。模式 % 不能匹配 NULL。

说明

OceanBase 数据库 Oracle 模式中满足以下条件，会将 like 改成 = 比较：

pattern 不包含 % 或 _ 通配符，没有 escape。
字段类型不是 LOB。Oracle 模式下 LOB 类型不支持 = 比较，因此不可转换。
pattern不是固定长度的 char 或 nchar 类型。

如下示例为查询员工名前四个字母为 ALLE，最后一个为任意字符的数据。

obclient> SELECT empname, deptno FROM emp WHERE empname LIKE 'ALLE_';

返回结果如下：

+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| ALLEN   |     35 |
+---------+--------+
1 row in set

如下示例为查询员工姓名首字母为 A 的数据。

obclient> SELECT empname, deptno FROM emp WHERE empname LIKE 'A%';

返回结果如下：

+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| ALLEN   |     35 |
+---------+--------+
1 row in set

注意

当数据库字集为 ASCII 时一个汉字需要两个 _；当字符集为 GBK 时只需要一个 _。

区间查询（BETWEEN AND）

操作符 BETWEEN ... AND 会选取介于两个值之间的数据。这些值可以是数值、文本或者日期。一般格式为：

SELECT column_name [,column_name...] FROM table_name WHERE
[NOT] BETWEEN min_const_value AND max_const_value;

注意

区间查询的两个临界值不要调换位置，只能是大于等于左边的值，并且小于等于右边的值。

示例如下：

obclient> SELECT * FROM emp WHERE sal BETWEEN 2000 AND 2999;

返回结果如下：

+-------+---------+---------+------+-----------+------+------+--------+------+
| EMPNO | EMPNAME | JOB     | MGR  | HIREDATE  | SAL  | COMM | DEPTNO | AGE  |
+-------+---------+---------+------+-----------+------+------+--------+------+
|  1566 | JONES   | MANAGER | 1839 | 02-APR-81 | 2975 | NULL |     40 |   22 |
|  1698 | BLAKE   | MANAGER | 1839 | 01-MAY-81 | 2850 | NULL |     30 |   33 |
+-------+---------+---------+------+-----------+------+------+--------+------+
2 rows in set

指定集合查询（IN）

操作符 IN 允许在 WHERE 子句中指定多个值，可以作为一个集合。IN 表示指定列中的数据只要满足集合中任意一个都会被返回。NOT IN 表示和集合中都不匹配的数据会被返回。一般格式为：

SELECT column_name [,column_name...] FROM table_name WHERE
column_name [NOT] IN (const_value,const_value,const_value...);

注意

[NOT] IN 集合中的值类型必须一致或相互兼容。
[NOT] IN 集合中的值不支持通配符。

示例如下：

obclient> SELECT * FROM emp WHERE deptno IN (30,40,50,60);

返回结果如下：

+-------+---------+---------+------+-----------+------+------+--------+------+
| EMPNO | EMPNAME | JOB     | MGR  | HIREDATE  | SAL  | COMM | DEPTNO | AGE  |
+-------+---------+---------+------+-----------+------+------+--------+------+
|  1566 | JONES   | MANAGER | 1839 | 02-APR-81 | 2975 | NULL |     40 |   22 |
|  1698 | BLAKE   | MANAGER | 1839 | 01-MAY-81 | 2850 | NULL |     30 |   33 |
+-------+---------+---------+------+-----------+------+------+--------+------+
2 rows in set

NULL 值专用查询（IS NULL/IS NOT NULL）

由于比较运算符、LIKE、BETWEEN AND、IN、NOT IN 对 NULL 值的查询结果不准确，所以建议使用 NULL 值专用查询语句 IS NULL 和 IS NOT NULL。

IS NULL

IS NULL 用于查询指定的列的值为 NULL 的数据。一般格式为：

SELECT column_name [,column_name...] FROM table_name WHERE
column_name IS NULL;

示例如下：

obclient> SELECT * FROM emp WHERE comm IS NULL;

返回结果如下：

+-------+---------+---------+------+-----------+------+------+--------+------+
| EMPNO | EMPNAME | JOB     | MGR  | HIREDATE  | SAL  | COMM | DEPTNO | AGE  |
+-------+---------+---------+------+-----------+------+------+--------+------+
|  1369 | SMITH   | CLERK   | 1902 | 17-DEC-80 |  800 | NULL |     20 |   22 |
|  1566 | JONES   | MANAGER | 1839 | 02-APR-81 | 2975 | NULL |     40 |   22 |
|  1698 | BLAKE   | MANAGER | 1839 | 01-MAY-81 | 2850 | NULL |     30 |   33 |
|  1788 | SCOTT   | ANALYST | 1566 | 15-JUL-87 | 3000 | NULL |     25 |   33 |
|  1902 | FORD    | ANALYST | 1566 | 05-DEC-81 | 3000 | NULL |     45 |   22 |
+-------+---------+---------+------+-----------+------+------+--------+------+
5 rows in set

IS NOT NULL

IS NOT NULL 用于查询指定的列的值为 NULL 的数据。一般格式为：

SELECT column_name [,column_name...] FROM table_name WHERE
column_name IS NOT NULL;

示例如下：

obclient> SELECT * FROM emp WHERE comm IS NOT NULL;

返回结果如下：

+-------+---------+----------+------+-----------+------+------+--------+------+
| EMPNO | EMPNAME | JOB      | MGR  | HIREDATE  | SAL  | COMM | DEPTNO | AGE  |
+-------+---------+----------+------+-----------+------+------+--------+------+
|  1499 | ALLEN   | SALESMAN | 1698 | 20-FEB-81 | 1600 |  300 |     35 |   22 |
+-------+---------+----------+------+-----------+------+------+--------+------+
1 row in set

分组查询

ORDER BY 子句用于对查询结果按照一个或多个属性列进行升序（ASC）或降序（DESC）排列，默认值为升序。

GROUP BY 支持单字段分组和多字段分组，使用 WHERE 子句可以在分组前对数据进行筛选，使用 HAVING 子句可以在分组后对数据进行筛选，使用 ORDER BY 子句可以在分组后对数据进行排序。

分组中常用的聚合函数如下表所示。

聚合函数	作用
MAX()	查询指定列的最大值。
MIN()	查询指定列的最小值。
COUNT()	统计查询结果的行数。
SUM()	返回指定列的总和。
AVG()	返回指定列数据的平均值。

分组查询示例

创建示例表 fruit_order。

CREATE TABLE fruit_order(
  order_id Number(10,2),
  user_id Number(10,2),
  user_name VARCHAR2(16),
  fruit_price Number(10,2),
  order_year Date,
  PRIMARY KEY (order_id)
);

插入适当测试数据。

INSERT INTO fruit_order(order_id,user_id,user_name,fruit_price,order_year) VALUES
  (1,1011,'张三',13.11,Date'2019-01-01'),
  (4,1011,'张三',22.21,Date'2020-01-01'),
  (6,1011,'张三',58.83,Date'2020-02-02'),
  (2,1022,'李四',23.34,Date'2019-02-02'),
  (3,1022,'李四',12.22,Date'2019-03-03'),
  (7,1022,'李四',14.66,Date'2021-03-03'),
  (8,1022,'李四',34.44,Date'2021-04-04'),
  (5,1033,'王五',51.55,Date'2020-05-05'),
  (9,1033,'王五',63.66,Date'2021-06-06');

单字段分组查询

查询每个客户下单数量，并输出客户 ID 和下单数量。

obclient [SYS]> SELECT user_id 客户ID, COUNT(order_id) 下单数量 FROM fruit_order GROUP BY user_id;

返回结果如下：

+----------+--------------+
| 客户ID   | 下单数量     |
+----------+--------------+
|     1011 |            3 |
|     1022 |            4 |
|     1033 |            2 |
+----------+--------------+
3 rows in set

多字段分组查询

查询每个客户每年下单数量，并输出客户 ID、下单年份和下单数量。

obclient [SYS]> SELECT user_id 客户ID, order_year 下单年份, COUNT(order_id) 下单数量 FROM fruit_order GROUP BY user_id,order_year;

返回结果如下：

+----------+--------------+--------------+
| 客户ID   | 下单年份     | 下单数量     |
+----------+--------------+--------------+
|     1011 | 01-JAN-19    |            1 |
|     1022 | 02-FEB-19    |            1 |
|     1022 | 03-MAR-19    |            1 |
|     1011 | 01-JAN-20    |            1 |
|     1033 | 05-MAY-20    |            1 |
|     1011 | 02-FEB-20    |            1 |
|     1022 | 03-MAR-21    |            1 |
|     1022 | 04-APR-21    |            1 |
|     1033 | 06-JUN-21    |            1 |
+----------+--------------+--------------+
9 rows in set

分组前筛选

查询 2020 年每个客户的下单数量，并输出客户 ID 和下单数量。

obclient [SYS]> SELECT user_id 客户ID, COUNT(order_id) 下单数量 FROM fruit_order t WHERE t.order_year = '01-JAN-20' GROUP BY user_id;

返回结果如下：

+----------+--------------+
| 客户ID   | 下单数量     |
+----------+--------------+
|     1011 |            1 |
+----------+--------------+
1 row in set

分组后筛选

查询 2019 年订单数量大于等于 1 的客户，并输出客户 ID 和下单数量。

obclient [SYS]> SELECT user_id 客户ID, COUNT(order_id) 下单数量 FROM fruit_order t WHERE t.order_year = '01-JAN-19' GROUP BY user_id HAVING COUNT(order_id) >= 1;

返回结果如下：

+----------+--------------+
| 客户ID   | 下单数量     |
+----------+--------------+
|     1011 |            1 |
+----------+--------------+
1 row in set

分组后排序

查询每个客户订单的最大金额，然后按照最大金额倒序输出客户 ID 和最大金额。

obclient [SYS]> SELECT user_id 客户ID, MAX(fruit_price) 最大金额 FROM fruit_order t GROUP BY user_id ORDER BY 最大金额 DESC;

返回结果如下：

+----------+--------------+
| 客户ID   | 最大金额     |
+----------+--------------+
|     1033 |        63.66 |
|     1011 |        58.83 |
|     1022 |        34.44 |
+----------+--------------+
3 rows in set

分组汇总

使用 GROUP BY CUBE 子句，对 fruit_order 表按照 user_id 进行分组，并对分组结果进行汇总。

obclient [SYS]> SELECT user_id,SUM(FRUIT_PRICE) FROM fruit_order GROUP BY CUBE(user_id);

返回结果如下：

+---------+------------------+
| USER_ID | SUM(FRUIT_PRICE) |
+---------+------------------+
|    NULL |           294.02 |
|    1011 |            94.15 |
|    1022 |            84.66 |
|    1033 |           115.21 |
+---------+------------------+
4 rows in set

更多分组汇总的信息，请参见 SIMPLE SELECT。

排序查询

ORDER BY 子句用于对查询结果按照一个或多个属性列进行升序（ASC）或降序（DESC）排列，默认为升序。

查询 2019 年订单数量大于等于 1 的客户，并输出客户 ID 和下单数量。

obclient [SYS]> SELECT user_id,USER_NAME,SUM(FRUIT_PRICE) FROM fruit_order GROUP BY CUBE(user_id,USER_NAME);

返回结果如下：

+---------+-----------+------------------+
| USER_ID | USER_NAME | SUM(FRUIT_PRICE) |
+---------+-----------+------------------+
|    NULL | NULL      |           294.02 |
|    NULL | 张三      |            94.15 |
|    NULL | 李四      |            84.66 |
|    NULL | 王五      |           115.21 |
|    1011 | NULL      |            94.15 |
|    1022 | NULL      |            84.66 |
|    1033 | NULL      |           115.21 |
|    1011 | 张三      |            94.15 |
|    1022 | 李四      |            84.66 |
|    1033 | 王五      |           115.21 |
+---------+-----------+------------------+
10 rows in set

单字段排序查询

按照 `deptno` 升序显示员工信息

obclient [SYS]> SELECT empname, deptno FROM emp ORDER BY deptno;

返回结果如下：

+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| SMITH   |     20 |
| SCOTT   |     25 |
| BLAKE   |     30 |
| ALLEN   |     35 |
| JONES   |     40 |
| FORD    |     45 |
+---------+--------+
6 rows in set

按照 `deptno` 降序显示员工信息

obclient [SYS]> SELECT empname, deptno FROM emp ORDER BY deptno DESC;

返回结果如下：

+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| FORD    |     45 |
| JONES   |     40 |
| ALLEN   |     35 |
| BLAKE   |     30 |
| SCOTT   |     25 |
| SMITH   |     20 |
+---------+--------+
6 rows in set

多字段排序查询

按照 deptno 升序，sal 降序显示员工信息。

obclient [SYS]> SELECT empname, deptno, sal FROM emp ORDER BY deptno ASC,sal DESC;

返回结果如下：

+---------+--------+------+
| EMPNAME | DEPTNO | SAL  |
+---------+--------+------+
| SMITH   |     20 |  800 |
| SCOTT   |     25 | 3000 |
| BLAKE   |     30 | 2850 |
| ALLEN   |     35 | 1600 |
| JONES   |     40 | 2975 |
| FORD    |     45 | 3000 |
+---------+--------+------+
6 rows in set

在 WHERE 子句之后排序

ORDER BY 可以在 WHERE 之后进行排序。示例如下：

obclient [SYS]> SELECT empname, deptno,sal, HIREDATE FROM emp WHERE sal>=1000 ORDER BY HIREDATE;

返回结果如下：

+---------+--------+------+-----------+
| EMPNAME | DEPTNO | SAL  | HIREDATE  |
+---------+--------+------+-----------+
| ALLEN   |     35 | 1600 | 20-FEB-81 |
| JONES   |     40 | 2975 | 02-APR-81 |
| BLAKE   |     30 | 2850 | 01-MAY-81 |
| FORD    |     45 | 3000 | 05-DEC-81 |
| SCOTT   |     25 | 3000 | 15-JUL-87 |
+---------+--------+------+-----------+
5 rows in set

使用 NLSSORT 函数排序

可以使用 NLSSORT 函数进行中文的拼音排序，笔画排序等。

有关 NLSSORT 函数的详细信息，请参见 NLSSORT。

按拼音排序

obclient [SYS]> SELECT empname, deptno,sal FROM emp ORDER BY NLSSORT(empname,'NLS_SORT=UCA0900_SCHINESE_PINYIN');

返回结果如下：

+---------+--------+------+
| EMPNAME | DEPTNO | SAL  |
+---------+--------+------+
| ALLEN   |     35 | 1600 |
| BLAKE   |     30 | 2850 |
| FORD    |     45 | 3000 |
| JONES   |     40 | 2975 |
| SCOTT   |     25 | 3000 |
| SMITH   |     20 |  800 |
+---------+--------+------+
6 rows in set

在查询中使用 Row_Limiting_Clause

Row_Limiting_Clause 用来限制 SELECT 查询返回的行数，常用于分页操作。

说明

更多有关分页查询的详细信息和最佳实践，请参见分页查询。

查询第 N 行以后的数据

示例 1

查询第 4 行以后的数据。

obclient> SELECT * FROM tb OFFSET 4 ROW;

返回结果如下：

+------+------+------+
| ID   | NAME | NUM  |
+------+------+------+
|    5 | b    |  700 |
|    6 | a    |   80 |
+------+------+------+
2 rows in set

示例 2

查询第 5 行以后的数据。

obclient> SELECT * FROM tb OFFSET 5 ROWS;

返回结果如下：

+------+------+------+
| ID   | NAME | NUM  |
+------+------+------+
|    6 | a    |   80 |
+------+------+------+
1 row in set

查询 id 最低的 3 条数据

obclient> SELECT * FROM tb ORDER BY id FETCH FIRST 3 ROWS ONLY;

返回结果如下：

+------+------+------+
| ID   | NAME | NUM  |
+------+------+------+
|    1 | a    |  100 |
|    2 | b    |  200 |
|    3 | a    |   50 |
+------+------+------+
3 rows in set

查询 id 最低的 2 条数据

obclient> SELECT id, name FROM tb ORDER BY id FETCH NEXT 2 ROWS ONLY;

返回结果如下：

+------+------+
| ID   | NAME |
+------+------+
|    1 | a    |
|    2 | b    |
+------+------+
2 rows in set

查询 num 中前 30% 的数据

obclient> SELECT id, name,num FROM tb ORDER BY num
 FETCH FIRST 30 PERCENT ROWS ONLY;

返回结果如下：

+------+------+------+
| ID   | NAME | NUM  |
+------+------+------+
|    3 | a    |   50 |
+------+------+------+
1 row in set

查询 num 最低数据中前 30% 的数据，以及与上一个示例中获取的最后一条数据相同的所有其他数据

obclient>  SELECT id, name,num FROM tb ORDER BY num FETCH FIRST 30 PERCENT ROWS WITH TIES;

返回结果如下：

+------+------+------+
| ID   | NAME | NUM  |
+------+------+------+
|    3 | a    |   50 |
+------+------+------+
1 row in set

前提条件

语法

SELECT 关键字执行顺序简介

注意

创建测试表并添加测试数据

基本查询

查询全部列

查询时指定表别名

查询指定列

在查询的同时为列设置别名

在查询时消除取值重复的行

在查询时可以使用 ROWNUM 用来限制返回的行数

查询经过计算的值

FROM 函数查询

数据过滤

比较条件查询

等于（=）

不等于（<> 和 !=）

大于（>）和小于（<）

说明

逻辑条件查询

并且（AND）

或者（OR）

模糊查询（LIKE）

说明

注意

区间查询（BETWEEN AND）

注意

指定集合查询（IN）

注意

NULL 值专用查询（IS NULL/IS NOT NULL）

IS NULL

IS NOT NULL

分组查询

分组查询示例

单字段分组查询

多字段分组查询

分组前筛选

分组后筛选

分组后排序

分组汇总

排序查询

单字段排序查询

按照 deptno 升序显示员工信息

按照 deptno 降序显示员工信息

多字段排序查询

在 WHERE 子句之后排序

使用 NLSSORT 函数排序

按拼音排序

在查询中使用 Row_Limiting_Clause

说明

查询第 N 行以后的数据

示例 1

示例 2

查询 id 最低的 3 条数据

查询 id 最低的 2 条数据

查询 num 中前 30% 的数据

查询 num 最低数据中前 30% 的数据，以及与上一个示例中获取的最后一条数据相同的所有其他数据

相关文档

在查询时可以使用 `ROWNUM` 用来限制返回的行数

按照 `deptno` 升序显示员工信息

按照 `deptno` 降序显示员工信息