SQLServer去重复数据

删除表中多余的重复记录,重复记录是根据单个字段(seasonId )来判断,只留有rowid最小的记录

分析

select seasonId ,count(1) FROM [HD_GameData].[dbo].[LZ_Season_List] group by seasonId having count(1)>1 order by seasonId

select min(id) FROM [HD_GameData].[dbo].[LZ_Season_List] group by seasonId having count(1)>1 order by seasonId

执行SQL

delete from [HD_GameData].[dbo].[LZ_Season_List] where seasonId

in ( select seasonId FROM [HD_GameData].[dbo].[LZ_Season_List] group by seasonId having count(1)>1 )

and

id not in ( select min(id) FROM [HD_GameData].[dbo].[LZ_Season_List] group by seasonId having count(1)>1)

查找表中多余的重复记录(多个字段,seasonId ,sportId )

select seasonId ,sportId FROM [HD_GameData].[dbo].[LZ_Season_List] group by seasonId,sportId having count(1)>1 order by seasonId

select min(id) FROM [HD_GameData].[dbo].[LZ_Season_List] group by seasonId,sportId having count(1)>1 order by seasonId

执行SQL:

delete from [HD_GameData].[dbo].[LZ_Season_List] where (seasonId,sportId)

in ( select seasonId ,sportId FROM [HD_GameData].[dbo].[LZ_Season_List] group by seasonId,sportId having count(1)>1 )

and

id not in ( select min(id) FROM [HD_GameData].[dbo].[LZ_Season_List] group by seasonId,sportId having count(1)>1)