SQL Serverはトリガを使用して重複する列を削除します(Delete duplicate rows using cursor in SQL Server)

2001 ワード

/*By Jiangong SUN*/
ここで私が使っているのは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の2つのカラムに基づいて、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;