ROW_NUMBER基本用法

最新推荐文章于 2024-09-24 14:39:23 发布

weixin_34151004

最新推荐文章于 2024-09-24 14:39:23 发布

阅读量182

点赞数

文章标签：面试大数据

原文链接：http://www.cnblogs.com/guochangshan/archive/2011/11/29/2267504.html

版权

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 ) 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