SQL-使用事务删除重复记录行

￡神魔★判官ぃ 2021-11-29 07:18 334阅读 0赞

![326890-20180614121238689-910999867.jpg][]

/*
        时间：2018-6-14
        人员：LDH
        功能：SQL-使用事务删除重复记录行
    */
    
    USE master;
    GO
    
    IF EXISTS
    (
        SELECT COUNT(1)
        FROM sys.databases
        WHERE name = 'TestDB'
    )
    BEGIN
        DROP DATABASE TestDB;
    END;
    
    CREATE DATABASE TestDB;
    GO
    
    USE TestDB;
    GO
    
    CREATE TABLE Students
    (
        Id INT IDENTITY(1, 1) PRIMARY KEY,
        StuName NVARCHAR(20) NOT NULL,
        StuAge INT CHECK (StuAge
                          BETWEEN 1 AND 150
                         )
            DEFAULT 1,
        StuAddress NVARCHAR(100)
            DEFAULT '地址未知'
    );
    
    INSERT INTO Students
    (   StuName,
        StuAge,
        StuAddress
    )
    VALUES
    ('LDH', 28, N'中国上海'),
    ('LDH', 28, N'中国上海'),
    ('Lucy', 21, N'中国北京'),
    ('Lily', 28, N'中国南京'),
    ('Jack', 28, N'中国合肥'),
    ('Tom', 28, N'中国新疆');
    
    SELECT Id,
        StuName,
        StuAge,
        StuAddress
    FROM Students;
    -------------------------------------------------------
    
    /*
        功能：SQL-使用事务删除重复记录行
    */
    
    -- 开始事务
    BEGIN TRAN Tran_DeleteSameInfo;
    
    DELETE FROM dbo.Students
    WHERE Id NOT IN
          (
              SELECT MIN(Id)
              FROM dbo.Students
              GROUP BY StuName,
                  StuAge,
                  StuAddress
          );
    
    IF @@ERROR = 0
    BEGIN
        -- 提交事务
        COMMIT TRAN Tran_DeleteSameInfo;
        PRINT 'Success';
    END;
    ELSE
    BEGIN
        -- 回滚事务
        ROLLBACK TRAN Tran_DeleteSameInfo;
        PRINT 'Failure';
    END;
    
    -- 删除之后在查询，看是否还有重复记录行
    SELECT Id,
        StuName,
        StuAge,
        StuAddress
    FROM Students;

转载于:https://www.cnblogs.com/LifeDecidesHappiness/p/9182451.html

[326890-20180614121238689-910999867.jpg]: /images/20211129/69e9e19683914f7e98df0c62573e42ac.png