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

最新推荐文章于 2021-12-14 10:48:40 发布

garcon1986

最新推荐文章于 2021-12-14 10:48:40 发布

阅读量1.2k

点赞数

分类专栏： SQL Server 文章标签： sql server delete insert manager table

本文链接：https://blog.csdn.net/garcon1986/article/details/6731128

版权

SQL Server 专栏收录该内容

15 篇文章 0 订阅

订阅专栏

/*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两列为基础，使用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;