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

这里我使用的是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;