楼主说的不是IDentity标识所以可以用: delete from 表 where id not in (select max(id) from 表 group by col1,col2) 注: col1,col2是其它分组列 --------------------- 或用: select 标识=identity(int,1,1),* into test from 表 delete from 表 where 标识 not in (select max(标识) from 表 group by col1,col2) go drop table 表 go alter table test drop column 标识 go sp_rename 'test','表' go
delete 表 from 表 t where exists(select 1 from 表 where ID=t.ID and 内容=t.内容 and 其他字段>t.其他字段)
不能以DISTINCT 方式选择TEXT ntext 数据类型怎么办 表是 techfruit
修改表結構,重新加一個子段,刪完了再把表結構修改過來ALTER TABLE admin ADD NewID int IDENTITY (1, 1) go DELETE FROM admin WHERE EXISTS (SELECT 1 FROM admin a WHERE a.newid > admin.newid AND admin.uid = a.uid AND admin.uname = a.uname AND admin.upass a.upass AND admin.udep = a.udep) goALTER TABLE admin DROP COLUMN NewID go
---------------------
或用:
select 标识=identity(int,1,1),* into test from 表
delete from 表 where 标识 not in (select max(标识) from 表 group by col1,col2)
go
drop table 表
go
alter table test
drop column 标识
go
sp_rename 'test','表'
go
from 表 t
where exists(select 1 from 表 where ID=t.ID and 内容=t.内容 and 其他字段>t.其他字段)
go
DELETE FROM admin WHERE EXISTS
(SELECT 1
FROM admin a
WHERE a.newid > admin.newid AND
admin.uid = a.uid AND
admin.uname = a.uname AND
admin.upass a.upass AND
admin.udep = a.udep)
goALTER TABLE admin DROP COLUMN NewID
go