SQL开发的程序10多万数据中有重复数据要删,怎么删!新手啊
本帖最后由 xujie7194 于 2013-01-25 21:13:46 编辑 --关于SQL2000中如何删除重复数据,求教,老是提示语法错误.SQL 支持多个不同类型的字段 in()吗?
delete from RECORD where
(DATE,CARD_NUM,PAY, DISTANCE)
in
(select DATE,CARD_NUM,PAY, DISTANCE from RECORD
where DATE between '2013-01-09 17:24:43' and '2013-01-10 17:19:51'
group by DATE,CARD_NUM,PAY, DISTANCE having count(*) > 1 )
and
corde_ID not in (select min(corde_ID) from RECORD GROUP by corde_ID having count(DATE) > 1 and count(CARD_NUM)>1 and count(PAY)>1 )
/*select DATE,CARD_NUM,PAY, DISTANCE from RECORD
where DATE between '2013-01-09 17:24:43'
and '2013-01-10 17:19:51'
group by DATE,CARD_NUM,PAY, DISTANCE
having count(*) > 1 这句话是完全没问题的 */
[解决办法]
delete from RECORD where
CHECKSUM(DATE,CARD_NUM,PAY, DISTANCE)
in
CHECKSUM(select DATE,CARD_NUM,PAY, DISTANCE from RECORD
where DATE between '2013-01-09 17:24:43' and '2013-01-10 17:19:51'
group by DATE,CARD_NUM,PAY, DISTANCE having count(*) > 1 )
and
corde_ID not in (select min(corde_ID) from RECORD GROUP by corde_ID having count(DATE) > 1 and count(CARD_NUM)>1 and count(PAY)>1 )
[解决办法]
corde_ID应该是PK吧?
你delete from RECORD
where corde_ID NOT in
(
select min(corde_ID)
from RECORD
where 。。。
)
就行了阿，不用这么复杂。
