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 )