如何删除一个表中两条相同记录中的其中一条?(在线等候)

[wangshirufeng]

如果在一个表中,有两条记录
除了一个clno是不一样的，其它字段全部相同，怎样删除其中一条？

[solidpanther]

delete from tablename where clno='你想删除的'

[pbsql]

检查所有记录，若存在clno不一样且其它字段全部相同的记录，则删除该记录：
delete from t where exists(select * from t a where a.col1=t.col1 and a.col2=t.col2 and ... and a.clno<>t.clno)

[caiyunxia]

如果有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 联合主键

alter table 表 add newfield int identity(1,1)

delete 表
where newfield not in(
select min(newfield) from 表 group by 除newfield外的所有字段
)

alter table 表 drop column newfield



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...)

[julieyan]

增加一个ID字段IDENTTITY为TREUE,再删除

[LoveSQL]

delete yourtable
where clno in
(select a.clno from
(select max(clno) as clno,field1,filed2,field3... from yourtable group by field1,filed2,field3... having count(*)>1)
a
)