删除表中多余的重复记录（多个字段），只留有id最小的记录的问题

[meiwn]

1、查找表中多余的重复记录（多个字段）
select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)

此句运行出错，
服务器: 消息 170，级别 15，状态 1，行 2
第 2 行: ',' 附近有语法错误。


2、删除表中多余的重复记录（多个字段），只留有rowid最小的记录
delete from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1) and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)

此句运行出错，
服务器: 消息 170，级别 15，状态 1，行 1
第 1 行: 'a' 附近有语法错误。
服务器: 消息 156，级别 15，状态 1，行 2
在关键字 'and' 附近有语法错误。

请高手帮忙修改一下错误。
我的目的是想删除表中两个字段的内容完全一样的数据，并且保留重复记录中的最小ID的数据，其余的全部删除

[JiangHongTao]

1.

delete a 
from vitae a,(select min(id) id,peopleId,seq from vitae group by peopleId,seq  having count(*) >  1)  b
where a.peopleId = b.peopleid and a.seq = b.seq and a.id <> b.id

[JiangHongTao]

1.

select a.* from vitae a,(select peopleId,seq from vitae group by peopleId,seq  having count(*) >  1)  b
where a.peopleId = b.peopleid and a.seq = b.seq

2.

delete a 
from vitae a,(select min(rowid) rowid,peopleId,seq from vitae group by peopleId,seq  having count(*) >  1)  b
where a.peopleId = b.peopleid and a.seq = b.seq and a.rowid <> b.rowid

[andylauxing]

delete from vitae a
where not exists(select peopleId,seq,min(rowid) as rowid
from vitae
where peopleId=a.peopleId and seq=a.seq and rowid=a.rowid
group by peopleId,seq having count(*)> 1)

[xingtianzhang2008]

..................

都看晕了了...给你写一个小例子...

Sql Code:

create table testTab
(
id int primary key identity(1,1),
name varchar(30) not null,
notetime smalldatetime default getdate()
)

Go

insert into testTab
select '张三',getdate()
union all select '张三',getdate()
union all select '张三',getdate()
union all select '小二',getdate()
union all select '小二',getdate()

select * from testTab
/*
1 张三 2008-03-02 09:26:00
2 张三 2008-03-02 09:26:00
3 张三 2008-03-02 09:26:00
4 小二 2008-03-02 09:26:00
5 小二 2008-03-02 09:26:00
*/

--根据您需要重复数据保留最小的数据,那么您需要的结果为1 张三 2008-03-02 09:26:00 4 小二 2008-03-02 09:26:00

--delete begin

select min(id) from testTab group by name--得到1和4,也就是最小的id,那么得到了,那么在删除不是1和4的id

delete testTab where id not in (select min(id) from testTab group by name)--(3 行受影响)

select * from testTab--自己看吧...

[小鱼吹泡泡啊啊啊]

123