c1,c2为关键字段 delete from TB a where a.rowid!=(select max(b.rowid) from TB b where b.c1=a.c1 and b.c2=a.c2)
用distinct 和临时表吧 select distinct * into #templeTable from tableName drop table tableName select * into tableName from #templeTable drop table #templeTable
GROUP BY 筛选一下。看看行不行。 搂住的数据表没建主建吗?
我有主键啊,但是是自然增长型的delete from TB a where a.rowid!=(select max(b.rowid) from TB b where b.c1=a.c1 and b.c2=a.c2) 是可以,但是我的不重复的单笔记录也给删掉了啊,况且再MYSQL中DELETE是不可以用delete from TB a 的,给表另起名字总是出错,我要是改为: delete from TB where TB.rowid!=(select max(b.rowid) from TB b where b.c1=TB.c1 and b.c2=TB.c2) 就把我的单笔记录也一并给干掉了,不行的啦
1、MYSQL不熟。 2、rowid换为你的自然增长型的主键。 3、不能起别名?select max(b.rowid) from TB b怎么可以? 4、即使delete from TB where TB.rowid!=(select max(b.rowid) from TB b where b.c1=TB.c1 and b.c2=TB.c2)也可以。是不是MYSQL对待rowid不一样?用第二条试。
delete from TB a where a.rowid!=(select max(b.rowid) from TB b where b.c1=a.c1 and b.c2=a.c2)
select distinct * into #templeTable from tableName
drop table tableName
select * into tableName from #templeTable
drop table #templeTable
搂住的数据表没建主建吗?
是可以,但是我的不重复的单笔记录也给删掉了啊,况且再MYSQL中DELETE是不可以用delete from TB a 的,给表另起名字总是出错,我要是改为:
delete from TB where TB.rowid!=(select max(b.rowid) from TB b where b.c1=TB.c1 and b.c2=TB.c2) 就把我的单笔记录也一并给干掉了,不行的啦
2、rowid换为你的自然增长型的主键。
3、不能起别名?select max(b.rowid) from TB b怎么可以?
4、即使delete from TB where TB.rowid!=(select max(b.rowid) from TB b where b.c1=TB.c1 and b.c2=TB.c2)也可以。是不是MYSQL对待rowid不一样?用第二条试。