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 BEGINCLOSE employeesCursorDEALLOCATE employeesCursorEND;DECLARE @Count INT;DECLARE @Code nvarchar(20);DECLARE @Value nvarchar(200);DECLARE employeesCursor CURSORFOR SELECT Code, Value, Count(*) - 1FROM employeesGROUP BY Code, ValueHAVING Count(*) > 1;BEGIN TRANOPEN employeesCursorFETCH NEXT FROM employeesCursor INTO @Code, @Value, @CountWHILE @@FETCH_STATUS = 0BEGINDELETE TOP(@Count) FROM employees WHERE Code = @Code AND Value = @ValueFETCH NEXT FROM employeesCursor INTO @Code, @Value, @CountENDCLOSE employeesCursorDEALLOCATE employeesCursorCOMMIT;
页:
[1]