T-SQL删除重复行，使用CTE保留一个ROW_NUMBER（）OVER PARTITION [英] T-SQL Remove Duplicate Rows, Keep one with ROW_NUMBER() OVER PARTITION using CTE

问题描述

我需要删除重复的行但保留一行。当我运行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行< br $> 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 lms
ROW_NUMBER() OVER (PARTITION 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
HAVING 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 t1

WHERE #Row > 1

这篇关于T-SQL删除重复行，使用CTE保留一个ROW_NUMBER（）OVER PARTITION的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！