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://*.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.

 

 

 

Windowed functions can only appear in the SELECT or ORDER BY clauses

上一篇:Android程序中,内嵌ELF可执行文件-- Android开发C语言混合编程总结


下一篇:03-Flutter移动电商实战-底部导航栏制作