ROW_NUMBER基本用法

2年前写得东西,现在搬到这,⼜补充了新的东西,虽然关于ROW_NUMBER有很多,但是⾃⼰写出来的东西可以加深理解,也不容易忘记!

　　项⽬中遇到的分页情况，⽤传统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：View Code

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 ) awhere 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 #t1select ROW_NUMBER() over(partition by id1,id2 order by id1) odid,* from #t1select 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 )awhere a.odid<2

结果都⼀样,只不过,distinct⽆法获取重复的项,如果⼤数据量去重的话,不知道效率如何,有待⽐较.

这东西技术⾯试的时候差不多都会问，希望能给将要⾯试的朋友提供点帮助，我⾃⼰也加深理解和记忆，强化记忆!暂时先这些！