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：

 

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

 

数据较少,只查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无法获取重复的项,如果大数据量去重的话,不知道效率如何,有待比较.

 

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

暂时先这些！