可以写一个存储过程实现,大概步骤如下: SELECT DISTINCT * INTO 临时表 FROM TABLE --把相同的记录过滤掉,存到临时表中 DELETE TABLE -- 删除原来临时表的内容 INSERT INTO TABLE SELECT * FROM 临时表 -- 把临时表恢复到原来的表里 DROP TABLE 临时表 -- 删除临时表这样做可以实现你的要求,可能效率低了一点。
什么dbms oracle的话可以 delete yourtable where rowid,col1,col2 not in (select max(rowid),col1,col2 from yourtable group by col1,col2);sql server的话用临时表解决吧 select distinct(col1),col2 into #temp from yourtable; delete yourtable; insert into yourtable select * from #temp
用一个存储过程实现 declare @a [char](10) declare @b [char](10) if begin @a=(select 字段1 from 表 where 条件) @b=(select 字段2 from 表 where 条件) delete from 表 where 条件 insert into 表(字段1,字段2) values(@a,@b) end
用一个存储过程实现 declare @a [char](10) declare @b [char](10) begin @a=(select 字段1 from 表 where 条件) @b=(select 字段2 from 表 where 条件) delete from 表 where 条件 insert into 表(字段1,字段2) values(@a,@b) end
同意xzou 如果你表中有步增长(ID)的字段的话,可以用下面语句来执行 delete yourtable from yourtable left join (select min(id) as id,fld1,fld2 group by fld1,fld2) a on yourtable.id=a.id where a.id is null
SELECT DISTINCT * INTO 临时表 FROM TABLE --把相同的记录过滤掉,存到临时表中
DELETE TABLE -- 删除原来临时表的内容
INSERT INTO TABLE SELECT * FROM 临时表 -- 把临时表恢复到原来的表里
DROP TABLE 临时表 -- 删除临时表这样做可以实现你的要求,可能效率低了一点。
oracle的话可以
delete yourtable where rowid,col1,col2 not in
(select max(rowid),col1,col2 from yourtable group by col1,col2);sql server的话用临时表解决吧
select distinct(col1),col2 into #temp from yourtable;
delete yourtable;
insert into yourtable select * from #temp
declare @a [char](10)
declare @b [char](10)
if
begin
@a=(select 字段1 from 表 where 条件)
@b=(select 字段2 from 表 where 条件)
delete from 表 where 条件
insert into 表(字段1,字段2) values(@a,@b)
end
declare @a [char](10)
declare @b [char](10)
begin
@a=(select 字段1 from 表 where 条件)
@b=(select 字段2 from 表 where 条件)
delete from 表 where 条件
insert into 表(字段1,字段2) values(@a,@b)
end
如果你表中有步增长(ID)的字段的话,可以用下面语句来执行
delete yourtable from yourtable left join (select min(id) as id,fld1,fld2 group by fld1,fld2) a on yourtable.id=a.id where a.id is null