多字段相同数据查询并删除多余数据

来源：互联网发布：淘宝禁止出售的商品编辑：程序博客网时间：2024/06/11 23:04

DECLARE @tabold TABLE

(

PAYEE_TVMEDIA_ID INT ,

TVMEDIA_ID INT ,

PayeeAccount_ID INT

)

DECLARE @taboldchuli TABLE

(

PAYEE_TVMEDIA_ID INT ,

TVMEDIA_ID INT ,

PayeeAccount_ID INT

)

INSERT @tabold

( PAYEE_TVMEDIA_ID ,

TVMEDIA_ID ,

PayeeAccount_ID

)

SELECT a.PAYEE_TVMEDIA_ID ,

a.PayeeAccount_ID ,

a.TVMEDIA_ID

FROM PAYEE_TVMEDIA a

INNER JOIN ( SELECT PayeeAccount_ID ,

TVMEDIA_ID

FROM PAYEE_TVMEDIA b

GROUP BY PayeeAccount_ID ,

TVMEDIA_ID

HAVING COUNT(*) > 1

) b ON a.PayeeAccount_ID = b.PayeeAccount_ID

AND a.TVMEDIA_ID = b.TVMEDIA_ID

ORDER BY a.PayeeAccount_ID ,

b.TVMEDIA_ID

INSERT INTO @taboldchuli

( PAYEE_TVMEDIA_ID ,

TVMEDIA_ID ,

PayeeAccount_ID

)

SELECT PAYEE_TVMEDIA_ID ,

TVMEDIA_ID ,

PayeeAccount_ID

FROM CAMSDB_NMS.dbo.PAYEE_TVMEDIA

DELETE FROM @taboldchuli

WHERE PAYEE_TVMEDIA_ID IN (

SELECT a.PAYEE_TVMEDIA_ID

FROM @tabold a

WHERE a.PAYEE_TVMEDIA_ID NOT IN ( SELECT MIN(b.PAYEE_TVMEDIA_ID)

FROM @tabold b

GROUP BY b.PayeeAccount_ID ,

b.TVMEDIA_ID ) )

SELECT *

FROM @taboldchuli

多字段相同数据查询 并删除多余数据

多字段相同数据查询并删除多余数据