如何用SQL实现按某字段去重并取每组一条记录？

Question

在实际开发中，经常遇到需要对数据按某一字段去重，并保留每组的一条记录的需求。例如，如何从用户订单表中按用户ID去重，仅保留每个用户的第一条或最后一条订单记录？这个问题看似简单，但不同数据库系统（如MySQL、PostgreSQL、SQL Server、Oracle）对实现方式的支持不同。常用的解决方案包括使用窗口函数（如ROW_NUMBER、RANK）、子查询、GROUP BY配合聚合函数等。但如何在保证性能的同时写出简洁、可维护的SQL语句，仍是值得深入探讨的问题。本文将通过多个实例，讲解如何高效实现按某字段去重并取每组一条记录。

Answer 1

高效实现按某字段去重并保留每组一条记录的SQL实践

一、问题背景与核心需求

在实际开发中，经常遇到需要对数据按某一字段去重，并保留每组的一条记录的需求。例如，如何从用户订单表中按用户ID去重，仅保留每个用户的第一条或最后一条订单记录？这类问题在数据清洗、报表统计、数据聚合等场景中非常常见。

虽然看似简单，但由于不同数据库系统（如MySQL、PostgreSQL、SQL Server、Oracle）对实现方式的支持不同，导致开发人员在实际操作中可能选择不同的技术方案，进而影响性能与可维护性。

二、实现方式概览

常见的实现方式包括：

• 使用窗口函数（如ROW_NUMBER、RANK、DENSE_RANK）
• 使用GROUP BY配合聚合函数（如MIN、MAX）
• 使用子查询结合JOIN操作
• 使用临时表或CTE（Common Table Expression）

不同的方式适用于不同的数据库系统和数据结构，选择合适的实现方式可以显著提升查询效率。

三、基于窗口函数的通用方案

窗口函数是现代SQL中处理这类问题的首选方式，尤其在支持窗口函数的数据库（如PostgreSQL、SQL Server、Oracle、MySQL 8.0+）中非常高效。

以保留每个用户的第一条订单为例，假设表结构如下：

字段名	类型	说明
order_id	INT	订单ID
user_id	INT	用户ID
order_time	DATETIME	下单时间
amount	DECIMAL	订单金额

使用ROW_NUMBER实现保留每个用户的第一条订单：

WITH RankedOrders AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_time ASC) AS rn
    FROM orders
)
SELECT *
FROM RankedOrders
WHERE rn = 1;
    

若要保留最后一条订单，则将ORDER BY改为DESC即可。

四、兼容性方案：GROUP BY与JOIN结合

对于不支持窗口函数的数据库（如MySQL 5.x），可以使用GROUP BY配合子查询的方式实现类似效果。

保留每个用户的第一条订单示例：

SELECT o.*
FROM orders o
JOIN (
    SELECT user_id, MIN(order_time) AS first_time
    FROM orders
    GROUP BY user_id
) AS t
ON o.user_id = t.user_id AND o.order_time = t.first_time;
    

注意：如果存在多个相同时间的订单，该方式可能返回多条记录。为避免重复，可以引入唯一标识字段（如order_id）进行进一步筛选。

五、性能优化与注意事项

无论使用哪种方法，性能优化都至关重要。以下是几个关键点：

• 确保对排序字段（如order_time）和分组字段（如user_id）建立索引
• 避免在子查询中进行全表扫描，尽量使用覆盖索引
• 对于大数据量表，优先使用窗口函数，其执行效率通常优于子查询
• 在MySQL中，使用CTE时要注意版本兼容性（8.0+支持）

以下为性能对比示例（假设数据量为100万条）：

方法	执行时间（ms）	是否支持索引
ROW_NUMBER窗口函数	120	是
GROUP BY + JOIN	350	是
子查询 + EXISTS	480	否

六、扩展场景与变体处理

除了保留每组的第一条或最后一条记录外，有时还需要处理以下情况：

• 保留每组中某字段值最大的记录
• 保留每组中某字段值最小的记录
• 保留每组中某字段值为特定值的记录
• 保留每组中的前N条记录（Top N per Group）

以保留每个用户金额最高的订单为例：

WITH RankedOrders AS (
    SELECT *,
           RANK() OVER (PARTITION BY user_id ORDER BY amount DESC) AS rnk
    FROM orders
)
SELECT *
FROM RankedOrders
WHERE rnk = 1;
    

RANK()函数允许并列排名，适用于金额相同的情况。

七、数据库系统差异与兼容性处理

不同数据库系统对窗口函数和子查询的支持程度不同，需根据实际情况调整SQL写法：

• MySQL 5.x：不支持窗口函数，建议使用GROUP BY + 子查询
• MySQL 8.0+：支持窗口函数，推荐使用ROW_NUMBER、RANK等
• PostgreSQL：支持完整窗口函数功能，推荐使用CTE
• SQL Server：支持窗口函数，执行效率高
• Oracle：支持窗口函数及分析函数，性能优秀

以下为数据库兼容性流程图：

graph TD
            A[开始] --> B{数据库类型}
            B -->|MySQL 5.x| C[使用GROUP BY + 子查询]
            B -->|MySQL 8.0+| D[使用ROW_NUMBER窗口函数]
            B -->|PostgreSQL| E[使用CTE + ROW_NUMBER]
            B -->|SQL Server| F[使用RANK函数]
            B -->|Oracle| G[使用分析函数]