SQL?Server删除重复数据只保留一条的实现步骤

SELECT col1, col2,  COUNT(*) AS count
FROM table_name
GROUP BY col1, col2
HAVING COUNT(*) > 1;

WITH cte AS (
    SELECT col1, col2, ROW_NUMBER() OVER(PARTITION BY col1, col2  ORDER BY (SELECT 0)) AS rn
    FROM talbe_name
)
DELETE FROM cte
WHERE rn > 1;

CREATE TABLE StudentCourses (
    StudentName varchar(50),
    CourseName varchar(50),
    Grade int
);

INSERT INTO StudentCourses (StudentName, CourseName, Grade)
VALUES ('张三', '数学', 85),
       ('张三', '英语', 90),
       ('李四', '数学', 95),
       ('李四', '英语', 80),
       ('王五', '数学', 90),
       ('王五', '英语', 85);

CREATE TABLE #TempTable (
    StudentName varchar(50),
    CourseName varchar(50),
    Grade int
);

INSERT INTO #TempTable (StudentName, CourseName, Grade)
SELECT StudentName, CourseName, Grade
FROM (
    SELECT StudentName, CourseName, Grade,
           ROW_NUMBER() OVER(PARTITION BY StudentName, CourseName ORDER BY Grade DESC) AS RowNum
    FROM StudentCourses
) AS Temp
WHERE RowNum = 1;

TRUNCATE TABLE StudentCourses;

INSERT INTO StudentCourses (StudentName, CourseName, Grade)
SELECT StudentName, CourseName, Grade
FROM #TempTable;

DROP