单个字段查询和删除

select a.* FROM CA_TRAIN_WEIGHT_LOG A,

(SELECT WEIGHT_CODE from CA_TRAIN_WEIGHT_LOG group by WEIGHT_CODE having count(*) > 1

) AS B

WHERE A.WEIGHT_CODE=B.WEIGHT_CODE AND A.ID >0

order by ID


delete FROM CA_TRAIN_WEIGHT_LOG WHERE EXISTS (

SELECT id FROM( SELECT WEIGHT_CODE FROM CA_TRAIN_WEIGHT_LOG GROUP BY WEIGHT_CODE

HAVING COUNT ( * ) > 1 ) tableabc

WHERE CA_TRAIN_WEIGHT_LOG.WEIGHT_CODE= tableabc.WEIGHT_CODE

)

AND id NOT IN (

SELECT MIN(id) FROM CA_TRAIN_WEIGHT_LOG GROUP BY WEIGHT_CODE HAVING COUNT ( * ) > 1)

多字段查询和删除

select a.WEIGHT_TIME,ID,a.TRAIN_INDEX,a.TRAIN_NO,a.TRAIN_NUM FROM CA_TRAIN_WEIGHT_LOG A,

(SELECT TRAIN_INDEX,TRAIN_NO,TRAIN_NUM from CA_TRAIN_WEIGHT_LOG group by TRAIN_INDEX,TRAIN_NO,TRAIN_NUM having count(*) > 1

) AS B

WHERE A.TRAIN_INDEX=B.TRAIN_INDEX AND A.TRAIN_NO=B.TRAIN_NO AND A.TRAIN_NUM=B.TRAIN_NUM AND A.ID >0

order by ID


delete FROM CA_TRAIN_WEIGHT_LOG WHERE EXISTS (

SELECT id FROM( SELECT TRAIN_INDEX,TRAIN_NO,TRAIN_NUM FROM CA_TRAIN_WEIGHT_LOG GROUP BY TRAIN_INDEX,TRAIN_NO,TRAIN_NUM

HAVING COUNT ( * ) > 1 ) tableabc

WHERE CA_TRAIN_WEIGHT_LOG.TRAIN_INDEX= tableabc.TRAIN_INDEX

AND CA_TRAIN_WEIGHT_LOG.TRAIN_NO= tableabc.TRAIN_NO

and CA_TRAIN_WEIGHT_LOG.TRAIN_NUM=tableabc.TRAIN_NUM )

AND id NOT IN (

SELECT MIN(id) FROM CA_TRAIN_WEIGHT_LOG GROUP BY TRAIN_INDEX,TRAIN_NO,TRAIN_NUM HAVING COUNT ( * ) > 1)