SQL 中的 DISTINCT 和 ROW_NUMBER() OVER() 有什么区别

在 SQL 中，DISTINCT 和 ROW_NUMBER() OVER() 是两种常用的数据处理方式，但它们的功能和使用场景有很大的不同。本文将详细探讨这两者的区别，并通过示例代码帮助读者更好地理解它们的应用场景。

1. DISTINCT 的作用与使用

1.1 DISTINCT 的基本概念

DISTINCT 是 SQL 中的一个关键字，用于从查询结果中去除重复的行。它作用于整个查询结果集，确保返回的每一行都是唯一的。

1.2 DISTINCT 的语法

SELECT DISTINCT column1, column2, ...
FROM table_name;

在这个语法中，DISTINCT 关键字会去除 column1, column2, ... 这些列的组合中重复的行。

1.3 DISTINCT 的示例

假设我们有一个 employees 表，结构如下：

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(50)
);

插入一些数据：

INSERT INTO employees (id, name, department) VALUES
(1, 'Alice', 'HR'),
(2, 'Bob', 'IT'),
(3, 'Charlie', 'HR'),
(4, 'David', 'IT'),
(5, 'Eve', 'HR');

如果我们想要查询所有不同的部门，可以使用 DISTINCT：

SELECT DISTINCT department
FROM employees;

结果将是：

department
----------
HR
IT

1.4 DISTINCT 的局限性

DISTINCT 只能去除完全相同的行。如果我们需要根据某些条件去除重复的行，或者需要保留某些特定的行，DISTINCT 就无法满足需求。

2. ROW_NUMBER() OVER() 的作用与使用

2.1 ROW_NUMBER() OVER() 的基本概念

ROW_NUMBER() OVER() 是 SQL 中的窗口函数（Window Function），它为每一行分配一个唯一的行号。这个行号是基于 OVER() 子句中指定的排序规则生成的。

2.2 ROW_NUMBER() OVER() 的语法

SELECT 
    column1, column2, ...,
    ROW_NUMBER() OVER (PARTITION BY columnX ORDER BY columnY) AS row_num
FROM table_name;

在这个语法中： - PARTITION BY columnX 是可选的，用于将数据分组，然后在每个组内进行编号。 - ORDER BY columnY 是必须的，用于指定排序规则。

2.3 ROW_NUMBER() OVER() 的示例

继续使用 employees 表，假设我们想要为每个部门的员工编号：

SELECT 
    id, name, department,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY id) AS row_num
FROM employees;

结果将是：

id  name    department  row_num
1   Alice   HR          1
3   Charlie HR          2
5   Eve     HR          3
2   Bob     IT          1
4   David   IT          2

在这个结果中，row_num 列表示每个部门内员工的编号。

2.4 ROW_NUMBER() OVER() 的应用场景

ROW_NUMBER() OVER() 常用于以下场景： - 去重：通过为每一行分配一个唯一的行号，可以轻松去除重复的行。 - 分页：在分页查询中，可以使用 ROW_NUMBER() 来生成行号，然后根据行号进行分页。 - 排名：在某些情况下，需要为数据排名，ROW_NUMBER() 可以用于生成排名。

3. DISTINCT 和 ROW_NUMBER() OVER() 的区别

3.1 功能上的区别

• DISTINCT：用于去除查询结果中的重复行，返回唯一的行。
• ROW_NUMBER() OVER()：为每一行分配一个唯一的行号，通常用于排序、去重或分页。

3.2 使用场景的区别

• DISTINCT：适用于简单的去重需求，当需要去除完全相同的行时使用。
• ROW_NUMBER() OVER()：适用于复杂的去重需求，例如根据某些条件去除重复的行，或者需要保留某些特定的行。

3.3 性能上的区别

• DISTINCT：在处理大数据集时，DISTINCT 可能会导致性能问题，因为它需要对整个结果集进行去重操作。
• ROW_NUMBER() OVER()：虽然 ROW_NUMBER() 也需要排序和编号，但通过合理的分区和排序，可以减少性能开销。

3.4 示例对比

假设我们有一个 orders 表，结构如下：

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    amount DECIMAL(10, 2)
);

插入一些数据：

INSERT INTO orders (order_id, customer_id, order_date, amount) VALUES
(1, 1, '2023-01-01', 100.00),
(2, 1, '2023-01-02', 200.00),
(3, 2, '2023-01-01', 150.00),
(4, 2, '2023-01-02', 250.00),
(5, 3, '2023-01-01', 300.00);

3.4.1 使用 DISTINCT 去重

如果我们想要查询所有不同的客户 ID，可以使用 DISTINCT：

SELECT DISTINCT customer_id
FROM orders;

结果将是：

customer_id
-----------
1
2
3

3.4.2 使用 ROW_NUMBER() OVER() 去重

如果我们想要查询每个客户的最新订单，可以使用 ROW_NUMBER() OVER()：

WITH ranked_orders AS (
    SELECT 
        order_id, customer_id, order_date, amount,
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS row_num
    FROM orders
)
SELECT order_id, customer_id, order_date, amount
FROM ranked_orders
WHERE row_num = 1;

结果将是：

order_id  customer_id  order_date  amount
2         1            2023-01-02  200.00
4         2            2023-01-02  250.00
5         3            2023-01-01  300.00

在这个结果中，我们只保留了每个客户的最新订单。

4. 总结

DISTINCT 和 ROW_NUMBER() OVER() 是 SQL 中两种不同的数据处理方式，它们的功能和使用场景有很大的不同。DISTINCT 适用于简单的去重需求，而 ROW_NUMBER() OVER() 则适用于复杂的去重、排序和分页需求。在实际应用中，应根据具体需求选择合适的工具。

通过本文的详细讲解和示例代码，相信读者已经对 DISTINCT 和 ROW_NUMBER() OVER() 的区别有了更深入的理解。在实际的 SQL 查询中，合理使用这两种工具，可以大大提高数据处理的效率和准确性。