What are the methods for removing duplicate queries in MySQL?

1. Insert test data

In the test data in the figure below, the users whose user_names are lilei and zhaofeng are duplicate data.

2. Method of removing duplicate data

1. Method 1: Use distinct

The code is as follows (example):

select distinct user_name,email,address from t_user;

As shown below, the data has been deduplicated and only 1 duplicate data is retained.

2. Method 2: Use group by

SELECT user_name,email,address 
	FROM t_user 
	GROUP BY user_name, email, address;

as shown below. The data has been deduplicated and only 1 duplicate data is retained.

3. Method 3: Use the window function

(1) If your database is MySQL8 or above, you can directly use the window function row_number( )

SELECT *
FROM(
    SELECT t.*, 
           ROW_NUMBER() OVER(PARTITION BY user_name
           ORDER BY last_login DESC) rn
    FROM table AS t
	) AS t_user
WHERE rn = 1;

(2) If your database version is lower than MySQL8, use the class row_number() method

select user_name, email, address 
from (
	select 
		b.*,
		@rownum := @rownum+1 ,-- 定义用户变量@rownum来记录数据的行号
		if(@pdept=b.user_name,@rank:=@rank+1,@rank:=1) as rank,-- 如果当前分组user_name和上一次分组user_name相同，则@rank（对每一组的数据进行编号）值加1，否则表示为新的分组，从1开始
		@pdept:=b.user_name -- 定义变量@pdept用来保存上一次的分组id
	from (select * from t_user) b ,
		(select @rownum :=0 , @pdept := null ,@rank:=0) c  -- 初始化自定义变量值
	order by b.user_name,b.last_login desc -- 该排序必须，否则结果会不对
) result
where rank = 1;

as shown below. The data has been deduplicated and only 1 duplicate data is retained.

The above is the detailed content of What are the methods for removing duplicate queries in MySQL?. For more information, please follow other related articles on the PHP Chinese website!