网站地图
【www.gdgbn.com--php与数据库】
--> --> (roy)生成
if not object_id("tempdb..#t") is null
drop table #t
go
create table #t([num] int,[name] nvarchar(1))
insert #t
select 1,n"a" union all
select 1,n"a" union all
select 1,n"a" union all
select 2,n"b" union all
select 2,n"b"
go
方法1:
if object_id("tempdb..#") is not null
drop table #
select distinct * into # from #t--排除重复记录结果集生成临时表#truncate table #t--清空表
insert #t select * from # --把临时表#插入到表#t中
--查看结果
select * from #t
/*
num name
----------- ----
1 a
2 b
(2 行受影响)
*/
--重新执行测试数据后用方法2
方法2:
alter table #t add id int identity--新增标识列
go
delete a from #t a where exists(select 1 from #t where num=a.num and name=a.name and id>a.id)--只保留一条记录
go
alter table #t drop column id--删除标识列
--查看结果
select * from #t
/*
num name
----------- ----
1 a
2 b
(2 行受影响)
*/
--重新执行测试数据后用方法3
方法3:
declare roy_cursor cursor local for
select count(1)-1,num,name from #t group by num,name having count(1)>1
declare @con int,@num int,@name nvarchar(1)
open roy_cursor
fetch next from roy_cursor into @con,@num,@name
while @@fetch_status=0
begin
set rowcount @con;
delete #t where num=@num and name=@name
set rowcount 0;
fetch next from roy_cursor into @con,@num,@name
end
close roy_cursor
deallocate roy_cursor
--查看结果
select * from #t
/*
num name
----------- ----
1 a
2 b
(2 行受影响)
*/
//利用存储过程
declare @max integer,@id integer
declare cur_rows cursor local for select 主字段,count(*) from 表名 group by 主字段 having count(*) > 1
open cur_rows
fetch cur_rows into @id,@max
while @@fetch_status=0
begin
select @max = @max -1
set rowcount @max
delete from 表名 where 主字段 = @id
fetch cur_rows into @id,@max
end
close cur_rows
set rowcount 0
//使用函数
select distinct * into #tmp from tablename
drop table tablename
select * into tablename from #tmp
drop table #tmp