sql 删除重复数据，保留重复数据最小ID

 

select * from test

ID Name
1 a
2 b
3 a
4 b
5 c

6 d

1. -- --标准sql方法：mysql、sql server--

2. -- 查询重复 方法一

3. select t.id,t.name from test t join(

4. select min(id) id,name from test group by name having(count(1)>1)

5. ) tt on t.id>tt.id and t.name=tt.name

6.  

7. -- 查询重复 方法二

8. select * from test where exists(

9. select * from (

10. select min(id) id,name from test group by name having(count(1)>1)

11. ) as tt where test.id>tt.id and test.name=tt.name

12. )

13.  

14. -- 删除重复 方法

15. delete from test where exists(

16. select * from (

17. select min(id) id,name from test group by name having(count(1)>1)

18. ) as tt where test.id>tt.id and test.name=tt.name

19. )

20.  

21. -- --sql server方法----

22. -- 查询

23. select * from (

24. select ROW_NUMBER() over(partition by name order by id ) row,id,name from test

25. ) t where t.row>1

26.  
27.  

28. -- 删除

29. delete from test where exists(

30. select * from

31. (

32. select ROW_NUMBER() over(partition by name order by id ) row,id,name from test

33. ) t where t.row>1 and test.id=t.id

34. )

select * from test

ID Name
1 a
2 b
5 c
6 d

 

 

本文转自： https://blog.csdn.net/guochunyang     原地址：  https://blog.csdn.net/guochunyang/article/details/79500554