清楚重复留一条 delete table where rowid no in (select max(t1.owid) from table t1 group by id, kh, name,sex);如果真的象你说的要删除所有重复数据delete from table t2 where rowid <=( select max(t1.rowid) from table t1 where t1.id=t2.id and t1.kh=t2.kh and t1.name=t2.name and t1.sex = t2.sex) e1.salary=e2.salary);
select Identity(int,1,1) as id_no,* into #tb from tabletruncate table tabledelete t from #tb t where id_no not in(select min(id_no) from table b where b.id = t.id and b.kh = t.kh and b.name = t.name and b.sex = t.sex group by b.id,b.kh,b.name,b.sex having count(*) >=1)insert into table(id,kh,name,sex) select id,kh,name,sex from #tbdrop table #tb
http://topic.csdn.net/t/20030728/17/2081611.html
delete table [表] where id in (select id from table) or kh in (select kh from table) or name in (select name from table) or sex in (select sex from table )
create table a ( uid int, uname varchar(50) ) insert into a values(1,'taiji') insert into a values(1,'taiji') insert into a values(1,'taiji') insert into a values(4,'cao') insert into a values(3,'ddd') insert into a values(4,'cao') insert into a values(7,'lijdjjie')create proc DeleteRow as declare @uid int,@count int --查询 create table aofa ( uid int, uname varchar(50) ) declare SelectR cursor for select uid from a open SelectR fetch next from SelectR into @uid select @count = count(*) from a where uid=@uid if @count >=2 insert into aofa select * from a where uid=@uid while(@@FETCH_STATUS=0) begin fetch next from SelectR into @uid select @count = count(*) from a where uid=@uid if @count >=2 insert into aofa select * from a where uid=@uid end close SelectR DEALLOCATE SelectR --删除 declare DeleteR cursor for select uid from a open DeleteR fetch next from DeleteR into @uid select @count = count(*) from a where uid=@uid if @count >=2 delete from a where uid=@uid while(@@FETCH_STATUS=0) begin fetch next from DeleteR into @uid select @count = count(*) from a where uid=@uid if @count >=2 delete from a where uid=@uid end close DeleteR DEALLOCATE DeleteR select * from aofaexec DeleteRow
delete table
where rowid no in (select max(t1.owid) from table t1 group by id, kh, name,sex);如果真的象你说的要删除所有重复数据delete from table t2 where rowid <=(
select max(t1.rowid) from table t1 where
t1.id=t2.id and t1.kh=t2.kh and t1.name=t2.name and t1.sex = t2.sex) e1.salary=e2.salary);
group by b.id,b.kh,b.name,b.sex
having count(*) >=1)insert into table(id,kh,name,sex)
select id,kh,name,sex from #tbdrop table #tb
where id in (select id from table) or kh in (select kh from table) or name in (select name from table) or sex in (select sex from table )
(
uid int,
uname varchar(50)
)
insert into a values(1,'taiji')
insert into a values(1,'taiji')
insert into a values(1,'taiji')
insert into a values(4,'cao')
insert into a values(3,'ddd')
insert into a values(4,'cao')
insert into a values(7,'lijdjjie')create proc DeleteRow
as
declare @uid int,@count int
--查询
create table aofa
(
uid int,
uname varchar(50)
)
declare SelectR cursor
for select uid from a
open SelectR
fetch next from SelectR into @uid
select @count = count(*) from a where uid=@uid
if @count >=2
insert into aofa select * from a where uid=@uid while(@@FETCH_STATUS=0)
begin
fetch next from SelectR into @uid
select @count = count(*) from a where uid=@uid
if @count >=2
insert into aofa select * from a where uid=@uid
end
close SelectR
DEALLOCATE SelectR
--删除
declare DeleteR cursor
for select uid from a
open DeleteR
fetch next from DeleteR into @uid
select @count = count(*) from a where uid=@uid
if @count >=2
delete from a where uid=@uid
while(@@FETCH_STATUS=0)
begin
fetch next from DeleteR into @uid
select @count = count(*) from a where uid=@uid
if @count >=2
delete from a where uid=@uid
end
close DeleteR
DEALLOCATE DeleteR
select * from aofaexec DeleteRow