Possible Duplicate:
Equivalent of LIMIT and OFFSET for SQL Server?可能重复：SQL Server的LIMIT和OFFSET的等价物？
I am working on a table which currently contains 2500 rows, and will be much more and more in future.
我正在研究一个目前包含2500行的表，将来会越来越多。
I am using jqgrid to display the records from the database .
我正在使用jqgrid来显示数据库中的记录。
Whenever the grid is loading, I am retrieving the top 500 rows from the database. However when I am started using pagination in the grid, the number of records is adding.
每当网格加载时，我都会从数据库中检索前500行。但是当我开始在网格中使用分页时，记录的数量正在增加。
Example : first the number of rows is TOP 100, then going onto the next page its increased to TOP 200 and so on in the query.
示例：首先行数为TOP 100，然后进入下一页，其增加到TOP 200，依此类推。
Now I want a solution to avoid this issue such that I can limit the rows instead of retrieving top n rows based on the grid. Say using a range or LIMIT
现在我想要一个解决方案来避免这个问题，这样我就可以限制行而不是根据网格检索前n行。假设使用范围或LIMIT
Example
例
When I am navigating to next page in grid, instead of TOP 200 rows I want to fetch rows from 101 to 200
当我在网格中导航到下一页时，我想要从101到200获取行，而不是前200行
select top 500
exe.id,
bat.BName,
bat.tid,
bat.freq,
exe.status,
exe.Msg,
exe.time,
exe.Fi
from
XXX exe,
YYY bat
where
exe.id=bat.id
order by
CONVERT(VARCHAR(10),
exe.time,
120) DESC,
exe.status,
exe.id DESC
Please guide me in this, as I am new to jQGrid and not much aware how to handle this DB related stuff
请指导我，因为我是jQGrid的新手并且不太了解如何处理这个与DB相关的东西
I also referred few links in * but can't understand
我还在*中引用了一些链接但无法理解
Thanks in advance
提前致谢
1 个解决方案
#1
5
You can use ROW_NUMBER(), the row_number() is not assigned until you query the data so if you want to retrieve the data by the row_number() value, then you will need to use CTE or a sub-select to get the value for use in a WHERE clause.
你可以使用ROW_NUMBER（），在查询数据之前不会分配row_number（），所以如果你想通过row_number（）值检索数据，那么你需要使用CTE或子选择来获取值用于WHERE子句。
SELECT *
FROM
(
select exe.id,
bat.BName,
bat.tid,
bat.freq,
exe.status,
exe.Msg,
exe.time,
exe.Fi,
row_number() over(order by CONVERT(VARCHAR(10), exe.time, 120) DESC,
exe.status, exe.id DESC) rn
from XXX exe,
YYY bat
where exe.id=bat.id
) x
WHERE rn between 101 and 200
I would also suggest a change in your JOIN syntax to use ANSI syntax:
我还建议您更改JOIN语法以使用ANSI语法：
SELECT *
FROM
(
select exe.id,
bat.BName,
bat.tid,
bat.freq,
exe.status,
exe.Msg,
exe.time,
exe.File,
row_number() over(order by CONVERT(VARCHAR(10), exe.time, 120) DESC,
exe.status, exe.id DESC) rn
from XXXexe
INNER JOIN YYY bat
ON exe.id=bat.id
) x
WHERE rn between 101 and 200
#1
5
You can use ROW_NUMBER(), the row_number() is not assigned until you query the data so if you want to retrieve the data by the row_number() value, then you will need to use CTE or a sub-select to get the value for use in a WHERE clause.
你可以使用ROW_NUMBER（），在查询数据之前不会分配row_number（），所以如果你想通过row_number（）值检索数据，那么你需要使用CTE或子选择来获取值用于WHERE子句。
SELECT *
FROM
(
select exe.id,
bat.BName,
bat.tid,
bat.freq,
exe.status,
exe.Msg,
exe.time,
exe.Fi,
row_number() over(order by CONVERT(VARCHAR(10), exe.time, 120) DESC,
exe.status, exe.id DESC) rn
from XXX exe,
YYY bat
where exe.id=bat.id
) x
WHERE rn between 101 and 200
I would also suggest a change in your JOIN syntax to use ANSI syntax:
我还建议您更改JOIN语法以使用ANSI语法：
SELECT *
FROM
(
select exe.id,
bat.BName,
bat.tid,
bat.freq,
exe.status,
exe.Msg,
exe.time,
exe.File,
row_number() over(order by CONVERT(VARCHAR(10), exe.time, 120) DESC,
exe.status, exe.id DESC) rn
from XXXexe
INNER JOIN YYY bat
ON exe.id=bat.id
) x
WHERE rn between 101 and 200