多字段查询和删除
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)