//先创建一个测试表
CREATE TABLE [dbo].[test](
[name] [nvarchar](50) NOT NULL,
[age] [int] NOT NULL
) ON [PRIMARY]
GO
//插入测试
INSERT INTO [dbo].[test] VALUES('张三',38)
INSERT INTO [dbo].[test] VALUES('张三',38)
INSERT INTO [dbo].[test] VALUES('李四',43)
INSERT INTO [dbo].[test] VALUES('李四',43)
INSERT INTO [dbo].[test] VALUES('王五',55)
GO
解决方案:
- 无唯一标识列
使⽤ROW_NUMBER()函数删除重复记录,根据每个人数据重复的规则进行分组(Partition By),我这边是以name和age去判断数据是否重复,order by后面随便跟一个字段即可,根据重复的数据进行编号。
SELECT Row_Number() OVER(PARTITION BY name, age ORDER BY name) AS RowNumber,* FROM test
SQL运行结果:
从结果可以看出,RowNumber已经根据你的分组,将每组数据进行编号。
最后将RowNumber大于1的进行Delete。
DELETE T FROM
(SELECT Row_Number() OVER(PARTITION BY name, age ORDER BY name) AS RowNumber,* FROM test)T
WHERE T.RowNumber > 1
- 有唯一标识列
使用HAVING()函数
把test表进行修改,添加id唯一标识列,新表数据如下:
SQL运行结果:
使用HAVING()函数,根据分组用COUNT求每组数量,大于1个的即为重复数据,再根据id取最大或最小,我这边是取最大。
SELECT MAX(ID) FROM Student GROUP BY name, age HAVING COUNT(*) > 1
得到重复的id后,根据id进行Delete。
DELETE FROM test
WHERE id NOT IN(SELECT MAX(ID) FROM Student GROUP BY name, age HAVING COUNT(*) > 1)
- 使用临时表(适用于数据量不大的情况)
//先将去重后的数据放入临时表内
SELECT DISTINCT name, age INTO #temp_test FROM test
//再将正式表内数据清空
DELETE FROM test
//最后把临时表数据导入正式表中
INSERT * INTO test SELECT * FROM #temp_test