id name
1 a
1 a
2 h
2 h
2 h
2 h
3 j
3 j
3 j
3 j
3 j
3 j
只取ID 重复的记录数除2 各ID的记录数只剩一半
通过删除数句后要的结果:
id name
1 a
2 h
2 h
3 j
3 j
3 j
1 a
1 a
2 h
2 h
2 h
2 h
3 j
3 j
3 j
3 j
3 j
3 j
只取ID 重复的记录数除2 各ID的记录数只剩一半
通过删除数句后要的结果:
id name
1 a
2 h
2 h
3 j
3 j
3 j
drop procedure [dbo].[prc]
GOSET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GOCREATE proc prc
as
begin
set nocount on
declare @s varchar(8000),@id varchar(10),@name varchar(20),@cnt int
create table #t(id varchar(10),name varchar(20),cnt int)
select * into #t1 from
(
select id,name,count(*)/2 as cnt from t group by id,name having count(*)>1
)a declare @cur Cursor
set @cur=Cursor for
select id,name,cnt from #t1
open @cur
fetch next from @cur into @id,@name,@cnt
while(@@fetch_status=0)
begin
while @cnt>0
begin
insert into #t values(@id,@name,@cnt)
set @cnt=@cnt-1
end
fetch next from @cur into @id,@name,@cnt
end
select id,name from #t
drop table #t
drop table #t1
close @cur
deallocate @cur
endGO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOexec prcid name
---------- --------------------
1 a
2 h
2 h
3 j
3 j
3 j
试一下下面的语句.
alter table 表 add 字段 int identity
delete from 表 where 字段%2=1
alter table 表 drop column 字段
如果要求是删除 1/4的记录
换成:delete from 表 where 字段%4=1,
其他百分比的也可以如此做.
(完整实现)补充:create table t1(id int,name varchar(10))
insert t1 select 1 , 'a'
union all select 1 ,'a'
union all select 2 ,'h'
union all select 2 ,'h'
union all select 2 ,'h'
union all select 2 ,'h'
union all select 3,'j'
union all select 3,'j'
union all select 3,'j'
union all select 3,'j'
union all select 3,'j'
union all select 3,'j'
union all select 4 ,'h'
union all select 5,'h'
alter table t1 add tid int identity
select * from t1
go
alter table kk add col int identity
select * from tb
delete from t1 where id in(select id from t1 group by id having count(id)>1 )
and tid%2=1
alter table t2 drop column tid
结果:
-------------
1 a
2 h
2 h
3 j
3 j
3 j
4 h
5 h
-------------
上面只删除,重复数据的 一半记录