ROW_NUMBER()用法
今天要分页,要用到ROW_NUMBER(),在网上找了一些关于ROW_NUMBER()用法的资料,
结果都没有说到了要点,浪费了好多时间!
下面以我的个人体会讲讲ROW_NUMBER()的用法,
1
ALTER PROCEDURE dbo.Flash_SearchSinger
2
@pageindex int, //页数;
3
@pagesize int, //每页的记录的条数
4
@singername nvarchar(30) //我做的是一个搜索页面,这是参数
5
AS
6
begin
7
with singer AS( //注意:这里的singer不是表名,而是你自己谁便取的一个临时表名!!!我就在这花了1个多小时!
8
SELECT ROW_NUMBER() OVER(ORDER BY AddTime DESC)ROW,ID,SingerName,PhotoAddress FROM SingerList where singername like '%'+@singername+'%')
9
10
SELECT SingerName AS'歌手姓名' ,PhotoAddress AS'图片地址',ID AS'singerid' FROM Singer //注意:这里查询的是我们刚刚用的临时表名,而不
11
//是数据库里的表名
12
Where ROW BETWEEN @pageindex*@pagesize AND @pageindex*@pagesize+@pagesize-1 //分页的页面
13
end
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
4
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
5
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
6
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
7
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
8
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
9
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
10
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
11
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
12
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
13
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
这就是ROW_NUMBER()的用法!
用法的效率很不错!
节省了原来SQL 2000里的好多代码!
本文所有权归作者,欢迎转载,但是请指明出处 :http://www.cnblogs.com/fly871117 ,严谨用于商业用途