mysql 某列去重后显示所有数据 mysql去重方式

转载

cnolnic 2024-05-31 21:28:13

文章标签 mysql 某列去重后显示所有数据 mysql 窗口函数结果集数据 文章分类 MySQL 数据库

引言：

在MySQL数据库中，数据去重是一项常见的操作。通过去除重复的数据，可以提高查询效率和数据的整洁度。本文将比较MySQL中常用的三种去重方式，包括使用DISTINCT关键字、使用GROUP BY子句和使用窗口函数ROW_NUMBER()，以帮助读者选择最适合自己的去重方法。

三种去重方法介绍：

使用DISTINCT关键字：
DISTINCT关键字是MySQL提供的一种去重方式。它可以用在SELECT语句中，去除查询结果中的重复记录。使用DISTINCT关键字的语法简单，只需要在SELECT关键字之后加上DISTINCT即可，这种方式适用于对单个列或多个列进行去重的情况。

SELECT DISTINCT column1, column2 FROM table_name

优点：

1.语法简单明了，容易理解和使用。
 2.可以同时对多个列进行去重，灵活性较好。

缺点：

1.DISTINCT关键字只能按照select的列进行去重，如果需要查询其他不用去重的列，需要额外处理。

使用GROUP BY子句：
GROUP BY子句是另一种常用的去重方式。它将查询结果按照指定的列进行分组，并对每个分组进行聚合操作。通过使用GROUP BY子句，我们可以将重复的记录分组合并，达到去重的效果。

优点：

1.可以对多个列进行分组和进行组内聚合操作，提供更灵活的功能。

缺点：

1.GROUP BY子句的语法比较复杂，学习和理解成本较高。
 2.如果需要返回不在group by里面的字段，需要设置sql_model参数。
 3.需要注意使用聚合函数处理分组后的数据。

使用窗口函数ROW_NUMBER()：
ROW_NUMBER()函数是SQL中的一个窗口函数，用于为每一行数据分配一个唯一的序列号，当根据需要的字段排序后，取序列号为1的数据，即可完成去重操作。

-- example
SELECT *
	FROM (
	   SELECT *, ROW_NUMBER() OVER (PARTITION BY column1, column2, ... ORDER BY column1, column2, ...) AS rn
	   FROM table
	) AS subquery
WHERE rn = 1;

优点：

1.ROW_NUMBER()函数是SQL标准中提供的窗口函数之一，使用广泛且简单易懂。
 2.可以根据需要按照分组后的结果的某列或者某几列再进行组内分组排序，然后通过将ROW_NUMBER()结果为1的行保留下来，可以实现对重复数据的去重操作。
 3.通过在ORDER BY子句中指定不同的列和排序顺序，可以灵活地根据具体需求进行去重和排序。

缺点：

1.只能去重连续行：ROW_NUMBER()函数只能对连续的行（分组后的组内）进行排序和排除，如果要处理非连续（分组后的非组内）的重复行，需要采用其他的方法。
 3.语法复杂：语法中涉及到嵌套查询和窗口函数的使用，相对于其他简单的去重方法，可能稍显复杂一些。

效率比较：

首先建立一个测试表,制造一些简单的测试数据，数据量大概在200w+，如果数据量少很难观察到效果：

-- test.out_order_item definition
CREATE TABLE `out_order_item` (
  `id` int NOT NULL AUTO_INCREMENT,
  `sale_order_id` int NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
);

没有索引的情况：

-- group by方式
select
	sale_order_id
from
	test.out_order_item
group by
	sale_order_id
limit 200;
-- 运行10次，2.5s左右
-- limit数量不受影响


-- distinct方式
select
	distinct sale_order_id
from
	test.out_order_item
limit 200;
-- 运行10次，5ms左右
-- limit 20000， 40ms左右
-- limit 2000000， 2.5s左右

-- row_number方式
select
	sale_order_id
from
	(select
		row_number() over(partition by sale_order_id) rn,
		sale_order_id
	from test.out_order_item) a
where
	rn = 1
limit 200;
-- 运行10次，5.5s左右
-- limit数量不受影响

有索引的情况：
先给表字段sale_order_id添加索引

CREATE INDEX sale_order_id_IDX USING BTREE ON test.out_order_item (sale_order_id);

-- group by方式
select
	sale_order_id
from
	test.out_order_item
group by
	sale_order_id
limit 200;
-- 运行10次，1ms左右
-- limit 20000，40ms左右
-- limit 2000000，1.2s左右


-- distinct方式
select
	distinct sale_order_id
from
	test.out_order_item
limit 200;
-- 运行10次，1ms左右
-- limit 20000， 40ms左右
-- limit 2000000， 1.5s左右

-- row_number方式
select
	sale_order_id
from
	(select
		row_number() over(partition by sale_order_id) rn,
		sale_order_id
	from test.out_order_item) a
where
	rn = 1
limit 200;
-- 运行10次，5.5s左右
-- limit数量不受影响