SQL Server 中ROW_NUMBER() OVER基本用法

　　项目中遇到的分页情况，用传统SQL select top 10 from a where guid not in (select top 10 from a) 这种分页 一但添加条件 数据量在百万级的话 执行的会很慢 ，如果加入ROW_NUMBER效率 会有大幅提升。基本原理是为sql构造一个自己的默认序号，外围SQL 通过查询这个已经排列好的序列号 ，就可实现分页 序号>1000 and 序号<2000 ，也就是1000-2000内的数据。

 

实际项目中应用的SQL：

select * from
(
select ROW_NUMBER()over(order by [基金账号]) 序号,

'0' as checkid, a.行名 as 支行编号, a.[Guid],a.[基金账号],a.姓名,a.证件号码, a.理财师ID, a.联系电话 联系电话, a.是否有效,

CASE when c.理财师姓名 is null then '否' else '是' end as 是否分配,

CASE when a.是否邀约 is null then '否' else '是' end as 是否邀约,

a.分配时间,a.诊断时间,

case when b.理财师姓名 is null then '--' else b.理财师姓名 end as 所属理财师 ,

case when a.理财师工作证号 is null then '--' else a.理财师工作证号 end as 所属理财师工作证号 ,

case when (select top 1 序列号 from 序列号 where 理财师工作证号=b.理财师工作证号 and 理财师工作证号 <> '')

is null then '--' else (select top 1 序列号 from 序列号 where 理财师工作证号=b.理财师工作证号 and 理财师工作证号 <> ''

)

end as 所属理财师序列号,

case when c.理财师姓名 is null then '--' else c.理财师姓名 end as 分配理财师,

case when c.理财师工作证号 is null then '--' else c.理财师工作证号 end as 分配理财师工作证号,

case when c.序列号 is null then '--' else c.序列号 end as 分配理财师序列号

from

客户视图 a

left join 理财师 b on a.理财师工作证号=b.理财师工作证号

left join 序列号 c on a.理财师序列号=c.序列号

left join 理财师 d on c.理财师工作证号=d.理财师工作证号

left join 机构字典 e on a.行名=e.代码

where c.理财师姓名 like '%谷谷~~~%'

) a where a.序号>0 and a.序号<=1000 

 

为方便理解再重新写一个简单的分页

建表和数据

 

数据较少,只查6-10的5条数据.

select * from (
select ROW_NUMBER()over( order by id1) orderid,* from #t1
) a where a.orderid between 6 and 10

 

ROW_NUMBER 还可以用查重复数据，1代表的是出现的次数,保留id2最大的,并把其他的删除掉.

delete a from 
(select ROW_NUMBER()over(partition by id1 order by id2 desc) orderid from #t1 ) a
where a.orderid>1

 

其中partition翻译为分区 分组,可以理解为group by

查询语句

select ROW_NUMBER() over(order by id1) odid,* from #t1
select ROW_NUMBER() over(partition by id1 order by id1) odid,* from #t1
select ROW_NUMBER() over(partition by id1,id2 order by id1) odid,* from #t1
select ROW_NUMBER() over(partition by id1,id2,id3 order by id1) odid,* from #t1

对应结果分别为

         

通过结果看,跟group by的效果差不多,更具体点区别暂时还未找到,google了一下,英文能力有限,并没有找到理想的答案,只知道group by在效率上要好一些,有空还是要找一下.

 

去重还有distinct

select distinct id1,id2,id3 from #t1

select * from (
select ROW_NUMBER() over(partition by id1,id2,id3 order by id1) odid,* from #t1 )a
where a.odid<2

结果都一样,只不过,distinct无法获取重复的项,如果大数据量去重的话,不知道效率如何,有待比较.