mysql根据多个字段删除重复记录只保留某字段最大值

目录

问题

目标 

解决思路

具体实现

注意事项

---

问题

   维护的一个系统之前由于数据录入时没有加重复判断导致库中很多记录重复的情况。

目标 

   只保留重复记录中某字段值最大的一条，其余删除。

解决思路

   首先将word和level完全相同的记录只保留id最大的记录，其次再保留相同word中level最大的记录。

具体实现

  表记录如下，需求是要将表中所有word重复的记录排重，只保留level最大的记录，其余删除。

                                                               filter_word表

id	word	level
1	AA	10
2	BB	5
3	CC	3
4	AA	10
5	AA	9
6	CC	3
7	BB	7
8	BB	8
9	DD	5
10	DD	10

步骤1：删掉表中完全相同的记录，只保留ID最大的一条。执行语句如下：

##查询完全相同的记录中，保留最大id的记录其余删除（每组重复记录中可能有多条）
delete from filter_word
       where id in(select id from(
								select id from filter_word a 
												where (a.word,a.level) in (
												   select * from(
														          select word,level from 
                                                                  filter_word group by 
                                                                  word,level 
                                                                  having count(*)>1
                                                                 ) YY
                                                ) order by word desc
								 ) II
				    ) 
			   and id not in (
                              select mid from(
			                                 select max(id) mid from (
												select * from filter_word
												where id in(
													select id from filter_word a 
													where (a.word,a.level) in(
														select * from(
																      select word,level 
                                                                      from filter_word
                                                                      group by word,level 
                                                                      having count(*)>1 
														) ZZ
													)
											    )		
										      ) SS group by SS.word
											) TT
					          )
			    order by word desc;	

步骤2：到这一步，表中已经没有完全相同的表记录了，然后再将word相同的表记录中，保留level最大的记录，其余删除。执行语句如下：

##查询word相同的记录中，保留最大level的记录其余删除（每组重复记录中可能有多条）
delete from filter_word 
            where id in(
                        select id from(
                                   select id from filter_word a 
								   where a.word in (
                                                    select * from(
															 select word from filter_word  
															 group by word having 
                                                             count(*)>1 
														     ) as YY 
												   ) 
									) II
					   )  
			and id not in( 
					      select id from(
										  select id from filter_word a inner join 
										  (
											select word,groupId,max(level) level from 
                                            filter_word group by word having count(*) > 1
										  )b 
										  on a.word=b.word 
										  and a.level=b.level  
										) WW
					     ) 
            order by word desc;	

---

注意事项

• 以上为直接执行了delete语句，这样是有风险的，实际是先将delete换成select语句执行确认没有问题再执行delete。
• 在此过程中，参考了很多up写的查询语句，发现如果直接执行第二条语句是无法达到预期结果的，因此先将完全相同的记录过滤掉再去根据level过滤。
• delete语句执行时遇到报错：You can‘t specify target table for update in FROM clause。原因在于不能在同一语句中，先select出同一表中的某些值，再update这个表。必须将select 的结果再通过一个中间表 select多一次并将为其命名避免该错误。