중복행 찾기 및 제거

1.  [user_id],[account_seq] 필드에서 중복행 찾기

SELECT [user_id],[account_seq], count(*) AS insertCount
FROM [dbo].[tbl_Assign]
GROUP BY [user_id],[account_seq]
HAVING count(*) > 1

2.  [user_id],[account_seq] 필드에서 가장 큰 seq 값 찾기

SELECT MAX(seq), [user_id],[account_seq], count(*) AS insertCount
FROM [dbo].[tbl_Assign]
GROUP BY [user_id],[account_seq]
HAVING count(*) > 1

3.  중복된 행 중 seq 가 가장 큰 값을 제외한 값 select

SELECT a.seq AS seq
FROM [dbo].[tbl_Assign] A 
INNER JOIN ( SELECT MAX(seq) as seq, [user_id],[account_seq], count(*) AS insertCount
		    FROM [dbo].[tbl_Assign]
		    GROUP BY [user_id],[account_seq]
                    HAVING count(*) >1 ) B
ON A.[user_id] = B.[user_id] and A.[account_seq] = B.[account_seq] and A.seq <> B.seq

4.  중복된 행 중 seq 가 가장 큰 값을 제외한 값 delete

DELETE FROM [dbo].[tbl_Assign]
WHERE seq IN (SELECT a.seq AS seq
                       FROM [dbo].[tbl_Assign] A 
                       INNER JOIN ( SELECT MAX(seq) as seq, [user_id],[account_seq], count(*) AS insertCount
                                           FROM [dbo].[tbl_Assign]
                                           GROUP BY [user_id],[account_seq]
                                           HAVING count(*) > 1) B
ON A.[user_id] = B.[user_id] and A.[account_seq] = B.[account_seq] and A.seq <> B.seq )