在写sql的时候经常会有这样的需求: 在一个table中会有多条重复的数据,如何有效的取出来不重复的数据,或者是删除掉重复的数据,或者取出某列重复值的第一条数据.
利用sql server 2005的新功能now_number和cte可以很好的实现.
举例说明如下:
建立测试数据:
代码如下:
create table dup1
(
col1 int null,
col2 varchar(20) null
)
insert into dup1 values
(1, 'aaa'),
(2, 'aaa'),
(2, 'aaa'),
(2, 'aaa'),
(3, 'bbb'),
(3, 'bbb'),
(4, 'ccc'),
(4, 'ddd'),
(5, 'eee')
select * from dup1
可以查看到重复的数据有:
代码如下:
select col1, col2, count(*) as dupcountfrom dup1group by col1, col2having count(*) > 1
接下来介绍如何delete掉重复的数据:
1.now_number:sql server 2005添加了很好用的ranking函数(now_number,rank,dense_rank,ntile),利用now_number()over(partition gy)最为直接,也最为方便,不能修改表或者产生多余的列.
首先会分配一个列号码,以col1,col2组合来分区排序.
代码如下:
select col1, col2,row_number() over (partition by col1, col2 order by col1) as rnfrom dup1
得到的序号如下:
很明显的是重复列都分组分割排序,只需要delete掉排序序号>1的即可.
代码如下:
--用到cte
with dupsd
as (
select col1, col2,
row_number() over (partition by col1, col2 order by col1) as rn
from dup1
)
delete dupsd
where rn > 1;
--或者
delete a from (
select col1, col2,
row_number() over (partition by col1, col2 order by col1) as rn
from dup1) a where a.rn>1
2.创建一个标识键唯一的表记一列.
代码如下:
alter table dbo.dup1
add
pk int identity
not null
constraint pk_dup1 primary key;
select *
from dup1;
删除找出与col1,col2相同并且比dup1.pk大的记录,也就是保留重复值中pk最小的记录.
代码如下:
delete dup1
where exists ( select *
from dup1 as d1
where d1.col1 = dup1.col1
and d1.col2 = dup1.col2
and d1.pk > dup1.pk );
3.select distant into,这种方法借助一个新的table,把不重复的结果集转移到新table中.
代码如下:
select distinct col1, col2 into nodupsfrom dup1;select * from nodups
建议采用第一种和第三种方法,第一种多见于t-sql的编程中,第三种在etl中常常使用.