如何把具有相同字段的记录删除,只留一条 - 编程论坛

#2

棉花糖ONE2007-01-09 15:59

id都不相同吗

#3

accpfriend2007-01-09 16:06

是的

#4

棉花糖ONE2007-01-09 16:16

if object_id('s') is not null
drop table s
go
create table s(id int identity,name varchar(10))
insert into s select 'aa'
union all select 'aa'

select * from s
delete from s where not exists ( select 1 from (select max(id) as id ,name from s group by name) m where id=s.id)

#5

accpfriend2007-01-09 16:25

谢谢,
测试下

#6

accpfriend2007-01-09 16:32

select * from s
delete from s where not exists ( select 1 from (select max(id) as id ,name from s group by name) m where id=s.id)
老兄,帮我讲解下,我看不太懂

#7

accpfriend2007-01-10 10:27

up

#8

chenxkfox2007-01-10 14:07

期待中。。。。。

#9

棉花糖ONE2007-01-10 14:08

delete from s where id in ( select top 1 id from s s1 where s1.name=s.name )这个简单点

#10

棉花糖ONE2007-01-10 14:11

以下是引用accpfriend在2007-1-9 16:32:14的发言：
select * from s
delete from s where not exists ( select 1 from (select max(id) as id ,name from s group by name) m where id=s.id)
老兄,帮我讲解下,我看不太懂

exists 我也没用过几次，这语句我是凭感觉写下来的．我只知道exists后面跟的是条件，还有个对于子查询来说非常关键的子查询中的表和外面的表有建立一个关联（我个人觉得，说实话对子查询不是很熟）

#11

Kendy1234562007-01-11 12:02

delete test where id not in (select name,max(id) from test group by name)

[此贴子已经被作者于2007-1-11 17:00:41编辑过]