澳门新蒲京娱乐


简易解决技巧

uniqueidentifier数据类型详解,全球唯一标识GUID

删除重复记录没有大小关系时

*/

/*num name———– —-1 a2 b

(2 行受影响)

sql 删除重复记录没有大小关系时,处理重复值

–查看结果select * from #t

–重新执行测试数据后用方法3方法3:

方法1:

declare roy_cursor cursor local forselect count(1)-1,num,name from
#t group by num,name having count(1)1declare @con int,@num int,@name
nvarchar(1)open roy_cursorfetch next from roy_cursor into
@con,@num,@namewhile @@fetch_status=0begin set rowcount @con; delete
#t where num=@num and name=@name set rowcount 0; fetch next from
roy_cursor into @con,@num,@nameendclose roy_cursordeallocate
roy_cursor

(2 行受影响)*/

— — (roy)生成

if object_id(‘tempdb..#’) is not null drop table #select distinct
* into # from #t–排除重复记录结果集生成临时表#

truncate table #t–清空表

insert #t select * from # –把临时表#插入到表#t中

(2 行受影响)*///利用存储过程

–查看结果select * from #t/*num name———– —-1 a2 b

select distinct * into #tmp from tablenamedrop table tablenameselect
* into tablename from #tmpdrop table #tmp

/*num name———– —-1 a2 b

if not object_id(‘tempdb..#t’) is null drop table #tgocreate table
#t([num] int,[name] nvarchar(1))insert #tselect 1,n’a’ union
allselect 1,n’a’ union allselect 1,n’a’ union allselect 2,n’b’ union
allselect 2,n’b’go

declare @max integer,@id integerdeclare cur_rows cursor local for
select 主字段,count(*) from 表名 group by 主字段 having count(*)
1open cur_rowsfetch cur_rows into @id,@maxwhile
@@fetch_status=0beginselect @max = @max -1set rowcount @maxdelete
from 表名 where 主字段 = @idfetch cur_rows into @id,@maxendclose
cur_rowsset rowcount 0

alter table #t add id int identity–新增标识列godelete a from #t a
where exists(select 1 from #t where num=a.num and name=a.name and
ida.id)–只保留一条记录goalter table #t drop column id–删除标识列

–查看结果select * from #t

//使用函数

–重新执行测试数据后用方法2方法2:

相关文章

No Comments, Be The First!
近期评论
    功能
    网站地图xml地图