删除相同内容的语句，请教

[qinqindodo]

table1
colum1 colum2 colum3 colum4
a b c d
a a c d
要求删除在各列中出现重复值的行 如同第二列一样，请教

[BenDan2002]

增加一个id字段，然后删除。

[qinqindodo]

有id字段，只是想找到一个高效的删除条件语句

[CrazyFor]

如果有ID字段，就是具有唯一性的字段

delect table where id not in (

select max(id) from table group by col1,col2,col3...
)
group by 子句后跟的字段就是你用来判断重复的条件，如只有col1，那么只要col1字段内容相同即表示记录相同。



2,如果是判断所有字段也可以这样
select * into #aa from table group by id1,id2,....
delete table
insert into table
select * from #aa



3，没有ID的情况

select identity(int,1,1) as id,* into #temp from tabel
delect # where id not in (
select max(id) from # group by col1,col2,col3...)
delect table
inset into table(...)
select ..... from #temp


col1+','+col2+','...col5 联合主键


select * from table where col1+','+col2+','...col5 in (

select max(col1+','+col2+','...col5) from table
where having count(*)>1
group by col1,col2,col3,col4
)
group by 子句后跟的字段就是你用来判断重复的条件，如只有col1，那么只要col1字段内容相同即表示记录相同。

2,
select identity(int,1,1) as id,* into #temp from tabel
select * from #temp where id in (
select max(id) from #emp where having count(*)>1 group by col1,col2,col3...)

[愉快的登山者]

没有ID：
select distinct * into #t from tabl1
go
drop table table1
go
insert table1 select * from #t
go
有ID：
delect table1 from tabl1 as A where id > (select min(id) from table1 where col1 = A.col1 and col2 = A.col2 and ......)

[zxjnew]

用自联接就可以很轻松搞定，看sqlserver的帮助有个类似的例子！

[kittyzheng]

1: insert table1 select distinct '***',colum2,colum3,colum4 from table1 where colum1='a' and colum2='b' and colum3='c' and colum4='d'

2: delete table1 where colum1='a' and colum2='b' and colum3='c' and colum4='d'

3: update table1 set colum1='a' where colum1='***'

[shb_zj]

delete from color where 图号 in (select 图号 from (select count(*) as num,图号 from color group by 图号) a where a.num>1)