Windowed functions can only appear in the SELECT or ORDER BY clauses
尝试做分页处理
select row_number over (orderby id asc) as rownum,*
from table
where rownum>=(@page*@pagesize-@pagesize) and rownum<=(@page*pagesize)
https://stackoverflow.com/questions/109232/what-is-the-best-way-to-paginate-results-in-sql-server
Getting the total number of results and paginating are two different operations. For the sake of this example, let's assume that the query you're dealing with is
SELECT * FROM Orders WHERE OrderDate >= '1980-01-01' ORDER BY OrderDate
In this case, you would determine the total number of results using:
SELECT COUNT(*) FROM Orders WHERE OrderDate >= '1980-01-01'
...which may seem inefficient, but is actually pretty performant, assuming all indexes etc. are properly set up.
Next, to get actual results back in a paged fashion, the following query would be most efficient:
SELECT *
FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum, *
FROM Orders
WHERE OrderDate >= '1980-01-01'
) AS RowConstrainedResult
WHERE RowNum >= 1
AND RowNum < 20
ORDER BY RowNum
This will return rows 1-19 of the original query. The cool thing here, especially for web apps, is that you don't have to keep any state, except the row numbers to be returned.
最新文章
- Microsoft SQL Server 2005 Service fails to start
- ContentProvider实现流程
- 014医疗项目-模块一:删除用户的功能l
- LeetCode Find the Duplicate Number 找重复出现的数(技巧)
- 比较实用的JavaScript库
- 在dom4j中使用XPath
- Python之路【第六篇】:socket
- c 语言练习__去掉多余的空白字符_修正
- DELPHI TDownLoadURL下载网络文件
- Node.js&;NPM的安装与配置(转)
- UVM:8.2.4 factory 机制的调试
- Roslyn and NRefactory
- k-d tree模板练习
- SpringBatch简介
- c# 基于文件系统实现的队列处理类
- C++多线程的使用
- POJ 3278 Catch That Cow(赶牛行动)
- 根据list集合某个字段进行排序
- SVG.Js事件示例,简单绑定拖动操作
- JavaScript 之 最佳位置选择
热门文章
- auto_ptr与shared_ptr
- mooc-IDEA 使用界面--001
- ArrayList,linkedList vecator的实现和区别
- 20191105 《Spring5高级编程》笔记-第16章
- python基础-5.1几种常见的排序算法
- 利用Redisson实现分布式锁及其底层原理解析
- java 中断线程的几种方式 interrupt()
- 谷歌浏览器安装插件时出现:程序包无效:“CRX_HEADER_INVALID"; 后的解决办法
- Thymeleaf运算符和表达式
- element 弹框关闭报错