DISTINCT去重排序_云原生数据库 PolarDB(PolarDB)-阿里云帮助中心

DISTINCT和ORDER BY是两个常用Oracle SQL语法的SQL子句。其中，DISTINCT用于消除查询结果中的重复记录，ORDER BY用于对查询结果进行排序。当这两个子句结合使用时，需要注意它们的语法和执行顺序。本文将详细介绍如何在Oracle SQL语法使用DISTINCT和ORDER BY，并通过示例帮助您更好地理解和应用。

基本语法

SELECT DISTINCT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

SELECT DISTINCT：选择唯一不同的记录，消除重复行。
FROM：指定查询的数据表。
WHERE：可选，指定查询条件。
ORDER BY：对结果集进行排序，可以指定一个或多个列，并选择升序 (ASC) 或降序 (DESC）。

工作原理

在SQL查询中，DISTINCT会在ORDER BY之前执行，即先筛选出唯一的记录，然后再对这些唯一记录进行排序。

使用示例

假设有一个名为employees的表，结构如下：

CREATE TABLE employees (
    employee_id INT PRIMARY KEY , -- 员工唯一标识
    first_name VARCHAR(50) NOT NULL,           -- 员工名字
    last_name VARCHAR(50) NOT NULL,            -- 员工姓氏
    department VARCHAR(50),                    -- 部门编号
    salary DECIMAL(10, 2) CHECK (salary > 0)   -- 薪资
);

INSERT INTO employees VALUES (1, 'John', 'Doe', 'Sales', 5000);
INSERT INTO employees VALUES (2, 'Jane', 'Smith', 'Marketing', 6000);
INSERT INTO employees VALUES (3, 'John', 'Doe', 'Sales', 5000);
INSERT INTO employees VALUES (4, 'Alice', 'Johnson', 'IT', 7000);
INSERT INTO employees VALUES (5, 'Bob', 'Brown', 'Sales', 5000);

选择唯一的部门并按名称排序。

SELECT DISTINCT DEPARTMENT
FROM employees
ORDER BY DEPARTMENT ASC;

返回结果如下：

 department 
------------
 IT
 Marketing
 Sales
(3 rows)

选择唯一的工资，并按工资降序排序。

SELECT DISTINCT SALARY
FROM employees
ORDER BY SALARY DESC;

返回结果如下：

 salary  
---------
 7000.00
 6000.00
 5000.00
(3 rows)

选择唯一的姓名组合并排序。

SELECT DISTINCT FIRST_NAME, LAST_NAME
FROM employees
ORDER BY FIRST_NAME ASC, LAST_NAME ASC;

返回结果如下：

说明

即使有多条记录的FIRST_NAME和LAST_NAME相同，DISTINCT只会返回一条唯一的组合。

 first_name | last_name 
------------+-----------
 Alice      | Johnson
 Bob        | Brown
 Jane       | Smith
 John       | Doe
(4 rows)

注意事项

ORDER BY中的列
在使用ORDER BY时，所指定的列必须出现在SELECT子句中。如果在ORDER BY中引用不在SELECT中的列，PolarDB将报错。
错误示例：
```
SELECT DISTINCT FIRST_NAME
FROM employees
ORDER BY LAST_NAME ASC;
```
报错如下：
```
ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list
```
然而，由于Oracle的兼容性原因，可以通过启用参数polar_enable_distinct_orderby_new_column来合法引用不在SELECT语句中的列。该用法使用步骤如下：
1. 启用polar_enable_distinct_orderby_new_column参数。
  说明
  PolarDB PostgreSQL版Oracle语法兼容 2.0在内核小版本2.0.14.26.0新增polar_enable_distinct_orderby_new_column参数。若集群的内核小版本低于该版本且需要合法引用不在SELECT语句中的列，请将内核小版本升级到2.0.14.26.0或以上版本。查询和升级内核小版本，请参见版本管理。
2. 在ORDER BY中引用不在SELECT中的列。
3. 查询末尾添加FETCH FIRST X rows ONLY或LIMIT字段。
```
SELECT DISTINCT FIRST_NAME
FROM employees
ORDER BY LAST_NAME ASC
FETCH FIRST 2 rows ONLY;
```
使用别名
支持为SELECT中的列指定别名，并在ORDER BY中使用这些别名。
```
SELECT DISTINCT FIRST_NAME AS FN
FROM employees
ORDER BY FN ASC;
```
ORDER BY列的位置
支持使用列的位置来排序，例如ORDER BY 1表示按SELECT中第一个列排序。
```
SELECT DISTINCT FIRST_NAME, LAST_NAME
FROM employees
ORDER BY 1 ASC;
```

高级用法

结合WHERE子句

在使用DISTINCT和ORDER BY时，可以添加WHERE子句以筛选特定记录。

示例：

SELECT DISTINCT DEPARTMENT
FROM employees
WHERE SALARY > 5000
ORDER BY DEPARTMENT ASC;

返回结果如下：

 department 
------------
 IT
 Marketing
(2 rows)

使用子查询

在某些复杂的查询场景中，可以使用子查询结合DISTINCT和ORDER BY。

示例：

假设需要先找出工资最高的每个部门，然后按部门名称排序：

SELECT DEPARTMENT, MAX(SALARY) AS MAX_SALARY
FROM employees
GROUP BY DEPARTMENT
ORDER BY DEPARTMENT ASC;

返回结果如下：

 department | max_salary 
------------+------------
 IT         |    7000.00
 Marketing  |    6000.00
 Sales      |    5000.00
(3 rows)

结合窗口函数

当需要在DISTINCT和ORDER BY的基础上进行更复杂的数据分析时，可以结合使用窗口函数。

示例：

选出每个部门中工资最高的员工，并按薪资降序排列。

SELECT DISTINCT DEPARTMENT, FIRST_NAME, LAST_NAME, SALARY
FROM (
    SELECT DEPARTMENT, FIRST_NAME, LAST_NAME, SALARY,
           ROW_NUMBER() OVER (PARTITION BY DEPARTMENT ORDER BY SALARY DESC) AS rn
    FROM employees
)
WHERE rn = 1
ORDER BY SALARY DESC;

返回结果如下：

 department | first_name | last_name | salary 
------------+------------+-----------+--------
 IT         | Alice      | Johnson   |   7000
 Marketing  | Jane       | Smith     |   6000
 Sales      | John       | Doe       |   5000
(3 rows)

常见错误与调试

使用不在WHERE中的列进行排序

如ORDER BY中的列描述，在ORDER BY使用未在SELECT子句中的列会导致错误。确保ORDER BY中的所有列都出现在SELECT中，或者使用别名或列位置。

说明

如果启用polar_enable_distinct_orderby_new_column参数，并满足FETCH FIRST X rows only，这种情况视为合法，此时DISTINCT将隐式引用ORDER BY中的列。

混淆DISTINCT的作用范围

DISTINCT适用于SELECT子句中列的整个组合，而不仅仅是单个列。因此，使用多个列的SELECT DISTINCT将基于所有指定列的组合来消除重复。

示例：

SELECT DISTINCT FIRST_NAME, DEPARTMENT
FROM employees
ORDER BY FIRST_NAME ASC;

以上SQL语句将消除FIRST_NAME和DEPARTMENT组合中的重复记录，而不仅仅是FIRST_NAME。

性能优化

在处理大数据集时，使用DISTINCT和ORDER BY可能会对查询性能产生影响。以下是一些优化建议：

索引优化：为WHERE、DISTINCT和ORDER BY使用的列创建适当的索引，加快查询速度。
减少返回的列数：只选择必要的列，避免不必要的数据处理。
使用分区：对大表进行分区，可以提高查询效率。
查询计划分析：使用EXPLAIN PLAN分析查询执行计划，发现潜在的性能瓶颈。

示例：

使用索引优化。

-- 创建索引以优化 DEPARTMENT 列的查询
CREATE INDEX idx_employees_department ON employees(DEPARTMENT);

总结

DISTINCT和ORDER BY是PolarDB SQL中功能强大的工具，用于消除重复记录和对结果集进行排序。了解其工作原理、正确语法及常见使用场景，有助于您编写高效且准确的查询。在实际应用中，根据具体需求合理组合这两个子句，并结合其他SQL功能，可以实现复杂的数据处理与分析任务。