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

/*
    时间：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;