SQL Server 中ROW_NUMBER() OVER基本用法

转载 2017年02月03日 16:24:37

  项目中遇到的分页情况,用传统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无法获取重复的项,如果大数据量去重的话,不知道效率如何,有待比较.


SQL Server数据库partition by 与ROW_NUMBER()函数使用详解

关于SQL的partition by 字段的一些用法心得 先看例子: if object_id('TESTDB') is not null drop table TESTDB create ta...
  • zzrshuiwuhen
  • zzrshuiwuhen
  • 2013年04月24日 10:03
  • 29053

SQL Server 批量更新字段值为ROW_NUMBER()+列名称

摘要:有这样一个需求,需要把数据表中的列名称的数据按行号重新排序并更新一下,这里用到了ROW_NUMBER()函数。 一:需求如下图: 二:通过执行如下sql语句实现了上面的需求,如下: ...
  • sxdtzhaoxinguo
  • sxdtzhaoxinguo
  • 2016年07月05日 13:51
  • 1602

SQLServer2005 Row_Number()详解

Sql Server 2000的自定义分页,要实现显示某一页,就返回那一页数据的效果的方法实在不尽人意.网上很多通用的分页存储过程,但看着就头大.如果使用我前面提到的使用in,not in,top来进...
  • jelink
  • jelink
  • 2006年08月30日 10:45
  • 2359

详述 SQL 中的 distinct 和 row_number() over() 的区别及用法

1 前言在咱们编写 SQL 语句操作数据库中的数据的时候,有可能会遇到一些不太爽的问题,例如对于同一字段拥有相同名称的记录,我们只需要显示一条,但实际上数据库中可能含有多条拥有相同名称的记录,从而在检...
  • qq_35246620
  • qq_35246620
  • 2017年02月22日 16:29
  • 9560

HIVESQL中row_number使用

row_number()在hive中是一个函数,必须带一个或者多个列参数,如row_number(col1, ....),它的作用是按指定的列进行分组生成行序列,在row_number(a,b) 时,...
  • timothy_lau
  • timothy_lau
  • 2016年05月24日 11:18
  • 2890

T-SQL ROW_NUMBER()函数的使用

ROW_NUMBER()函数是Sql 2005中新添的一个函数。通常它被用在分页的SQL语句中。 微软官方的对此函数的描述是:返回结果集分区内行的序列号,每个分区的第一行从 1 开始。 我对此的理...
  • zjut_simpson
  • zjut_simpson
  • 2012年08月03日 16:43
  • 1525

SqlServer 排名函数(row_number、rank、dense_rank)的比较

排名函数是SQL Server2005新加的功能。在SQL Server2005中有如下四个排名函数: 1. row_number 2. rank 3. dense_rank 4. ntile ...
  • chenghaibing2008
  • chenghaibing2008
  • 2013年02月27日 11:36
  • 3504

【SQL】—Oracle之row_number()与rownum效率大比拼

取前面11条数据的时间:rownum
  • u012654963
  • u012654963
  • 2016年08月05日 15:21
  • 2254

SqlServer四种排序:ROW_NUMBER()/RANK()/DENSE_RANK()/ntile() over()

今天女票问我SqlServer的四种排序,当场写了几句Sql让她了解,现把相关Sql放上来。 首先,我们创建一些测试数据。 if OBJECT_ID('Tempdb.dbo.#Tmp'...
  • a5685263
  • a5685263
  • 2016年08月11日 22:05
  • 1562

再看case when 在row_number中的使用

SELECT tpbt.PMName, k.[ActivityName], ...
  • zghnpdswyp
  • zghnpdswyp
  • 2016年12月14日 09:53
  • 579
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:SQL Server 中ROW_NUMBER() OVER基本用法
举报原因:
原因补充:

(最多只允许输入30个字)