DISTINCT去重排序 - 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 
------------
 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 
------------+-----------
 Alice      | Johnson
 Bob        | Brown
 Jane       | Smith
 John       | Doe
(4 rows)

```
SELECT DISTINCT FIRST_NAME
FROM employees
ORDER BY LAST_NAME ASC;
```
```
ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list
```
3. ```
SELECT DISTINCT FIRST_NAME
FROM employees
ORDER BY LAST_NAME ASC
FETCH FIRST 2 rows ONLY;
```
```
SELECT DISTINCT FIRST_NAME AS FN
FROM employees
ORDER BY FN ASC;
```
```
SELECT DISTINCT FIRST_NAME, LAST_NAME
FROM employees
ORDER BY 1 ASC;
```

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

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

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)

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)

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

-- 建立索引以最佳化 DEPARTMENT 列的查詢
CREATE INDEX idx_employees_department ON employees(DEPARTMENT);

基本文法

工作原理

使用樣本

注意事項

進階用法

常見錯誤與調試

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

混淆DISTINCT的作用範圍

效能最佳化

總結