SQL Server -删除具有多对多关系的表中的重复行

-- 创建示例表
CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    StudentName NVARCHAR(100)
);

CREATE TABLE Courses (
    CourseID INT PRIMARY KEY,
    CourseName NVARCHAR(100)
);

CREATE TABLE StudentCourses (
    StudentID INT,
    CourseID INT,
    CONSTRAINT PK_StudentCourses PRIMARY KEY (StudentID, CourseID),
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);

-- 插入示例数据
INSERT INTO Students (StudentID, StudentName) VALUES (1, 'Alice'), (2, 'Bob');
INSERT INTO Courses (CourseID, CourseName) VALUES (101, 'Math'), (102, 'Science');
INSERT INTO StudentCourses (StudentID, CourseID) VALUES (1, 101), (1, 101), (2, 102);

-- 删除重复行
WITH DuplicateRows AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY StudentID, CourseID ORDER BY (SELECT NULL)) AS RowNum
    FROM StudentCourses
)
DELETE FROM StudentCourses
WHERE StudentID IN (SELECT StudentID FROM DuplicateRows WHERE RowNum > 1);