T-SQL删除重复行，使用CTE保留一个ROW_NUMBER（）OVER PARTITION

本文介绍了T-SQL删除重复行，使用CTE保留一个ROW_NUMBER（）OVER PARTITION的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 我需要删除重复的行但保留一行。当我运行select语句时，我得到了很多结果。如果我运行WHERE EXISTS语句，我得到的结果数量正确。如何使用带有EXISTS语句的  ROW_NUMBER（）OVER PARTITION 是否删除重复项？I need to remove duplicate rows but keep one. When I run the select statement I'm getting to many results. If I run the WHERE EXISTS statement I'm getting the right number of results. How do you use the ROW_NUMBER() OVER PARTITION with an EXISTS statement do remove the duplicates? - WITH CTE AS - （ $ SELECT  --top 10  --ROW_NUMBER（）OVER（[ScheduleDaysID]分区ORDER BY [ScheduleDaysID]） AS行，* FROM LMS_Attendance为lms ROW_NUMBER（）OVER（PARIDTION BY UserID，ScheduleDaysID ORDER BY UserID，ScheduleDaysID）AS Row，* FROM LMS_Attendance as lms WHERE EXISTS （ SELECT UserID，ScheduleDaysID FROM [dbo]。[LMS_Attendance] WHERE UserID = UserID AND ScheduleDaysID = ScheduleDaysID GROUP BY UserID，ScheduleDaysID 有COUNT（*）> 1 - 结果88行 b $ b） - ） - 从CTEDELETE --WHERE ROW<> 1 --WITH CTE AS--(SELECT --top 10 --ROW_NUMBER() OVER (PARTITION BY [ScheduleDaysID] ORDER BY [ScheduleDaysID]) AS Row, * FROM LMS_Attendance as lmsROW_NUMBER() OVER (PARTITION BY UserID,ScheduleDaysID ORDER BY UserID,ScheduleDaysID) AS Row, * FROM LMS_Attendance as lmsWHERE EXISTS(SELECT UserID, ScheduleDaysIDFROM [dbo].[LMS_Attendance]WHERE UserID = UserID AND ScheduleDaysID = ScheduleDaysIDGROUP BY UserID, ScheduleDaysIDHAVING COUNT(*) > 1--results 88 rows)--)--DELETE FROM CTE--WHERE ROW <> 1推荐答案 嗨Tom SELECT *FROM( SELECT ROW_NUMBER() OVER (PARTITION BY UserID,ScheduleDaysID ORDER BY UserID,ScheduleDaysID) AS #Row, * FROM LMS_Attendance as lms) AS t1WHERE #Row > 1 这篇关于T-SQL删除重复行，使用CTE保留一个ROW_NUMBER（）OVER PARTITION的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！