计算机数据

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

-- -- (roy)生成

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

方法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 a2 b

(2 行受影响)*/

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

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

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

(2 行受影响)

*/

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

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

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

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

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

//使用函数

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

其他新闻
  •  安装前的准备: 1.Apache2.2.22.PHP5.2.173.SqlServer20084.sqlncli.msi(SqlServer客户端,可以到微软官方网站去下载)5.ntwdblib.dll (2000.80.194.0)6.SQLSRV20.exe,即MicrosoftDriver 2.0 for PHP for SQL Server(sql server...
    2020-02-10
  • 通常来说在php读取大文件的时候,我们采用的方法一般是一行行来讲取,而不是一次性把文件全部写入内存中,这样会导致php程序卡死,下面就给大家介绍这样一个例子。 paip.文件读写...
    2020-02-10
  • function hex2rgb($hex) {$color = str_replace('#','',$hex);$rgb = array('r' = hexdec(substr($color,0,2)),'g' = hexdec(substr($color,2,2)),'b' = hexdec(substr($color,4,2)));return $rgb;} // www.jb51.netfunction rgb2cmyk($var1,$g=0,$b=0) {if (...
    2020-02-10
友情链接

公司名称巴黎人电玩
版权所有:Copyright © 2015-2019 http://www.zhongqiangjy.com. 巴黎人电玩有限公司 版权所有

友情链接

Copyright © 2015-2019 http://www.zhongqiangjy.com. 巴黎人电玩有限公司 版权所有
公司地址http://www.zhongqiangjy.com