SQL Server使用触发器删除重复的列(Delete duplicate rows using cursor in SQL Server)

SQL删除重复记录

最新推荐文章于 2021-04-20 14:30:34 发布

最新推荐文章于 2021-04-20 14:30:34 发布 · 196 阅读

本文介绍了一种使用SQL的cursor和top函数结合的方法来删除数据库表中重复的数据行。通过具体的SQL脚本示例展示了如何针对特定列（如Code和Value）进行重复数据的识别与删除。

这里我使用的是cursor 加上top函数来删除重复的列的。

先建立一个表格

Create table employees(code nvarchar(20) Primary key, value nvarchar(200), description nvarchar(1000));

插入数据

Insert into employees(code, value, description) values('garcon1986', 'developer', 'he works in IT company.'); Insert into employees(code, value, description) values('garcon1986', 'developer', 'he works in IT company.'); Insert into employees(code, value, description) values('garcon1986', 'developer', 'he works in IT company.'); Insert into employees(code, value, description) values('garcon1986', 'developer', 'he works in IT company.'); Insert into employees(code, value, description) values('charles', 'IT manager', 'he owns an IT company.'); Insert into employees(code, value, description) values('charles', 'IT manager', 'he owns an IT company.'); Insert into employees(code, value, description) values('charles', 'IT manager', 'he owns an IT company.');

以Code和Value两列为基础，使用cursor和top函数删除重复的列

IF CURSOR_STATUS('local','employeesCursor')>0 BEGIN CLOSE employeesCursor DEALLOCATE employeesCursor END; DECLARE @Count INT; DECLARE @Code nvarchar(20); DECLARE @Value nvarchar(200); DECLARE employeesCursor CURSOR FOR SELECT Code, Value, Count(*) - 1 FROM employees GROUP BY Code, Value HAVING Count(*) > 1; BEGIN TRAN OPEN employeesCursor FETCH NEXT FROM employeesCursor INTO @Code, @Value, @Count WHILE @@FETCH_STATUS = 0 BEGIN DELETE TOP(@Count) FROM employees WHERE Code = @Code AND Value = @Value FETCH NEXT FROM employeesCursor INTO @Code, @Value, @Count END CLOSE employeesCursor DEALLOCATE employeesCursor COMMIT;