SQL查询asp.net分页格 [英] SQL query for asp.net grid pagination

问题描述

我使用 iBatis的和的SQLServer ，

什么是用最好的方式为pagging查询偏移和限制？

What is the best way for using offset and limit for pagging queries?

也许我加为ROWNUM 列 ROW_NUMBER（）OVER（ORDER BY编号），但这只会prevent数据简单的查询访问​​。有一些情况下，我使用选择的工会。如何来最佳这些查询？

Maybe I add the column ROW_NUMBER() OVER (ORDER BY Id) AS RowNum, but this will only prevent data access for simple queries. There is some cases that I use union of selects. How to optmize these queries?

推荐答案

我不知道ibatis的东西，但我想你可以在SQL做到这一点。

I don't know anything about ibatis, but I guess you could do this in SQL.

如果我理解正确，你想要得到分页几个select语句select语句或联合的结果。

If I understand you correctly, you want to get paginate the results of a select statement or union of a few select statements.

我会做下列方式。这可能是例如一个存储过程，并有可能应该理智一些在那里检查检查的价值观偏移和限制都大于0。如果你这样做最终会做这样的事情，一定要替换 * 与列名呢！

I'd do it the following way. This could be a stored procedure for example, and there should probably be some sanity checking in there the check the values of offset and limit are greater than 0. If you do end up doing something like this, make sure you replace * with your column names too!

下面是一个联合的例子：

Here is an example with a union:

DECLARE @offset INT;
DECLARE @limit INT;

WITH cte
     AS (SELECT t.*,
                Row_number() OVER (ORDER BY Id) AS RowNum
         FROM   (SELECT *
                 FROM   Table1
                 UNION
                 SELECT *
                 FROM   Table2) t)
SELECT *
FROM   cte
WHERE  RowNum BETWEEN @offset AND @offset + @limit

基本上我做了什么是来源于两个查询的并集一个新表，因为你说你的情况可能发生。我然后在 CTE ，那么只有选择>在 @Offset @limit + @Offset 找回只有你自找的。行

Essentially what I've done is derived a new table from the union of two queries, as you said could happen in your case. I'm then adding a column with the row number to the result of that in a CTE, then only selecting the rows specified in @Offset and @limit + @offset to get back only the rows you've asked for.

例如。设置 @Offset = 50 和 @limit = 50 ，你会得到返回结果50-100（如由排序在 ROW_NUMBER OVER子句指定的条件。

E.g. Setting @offset = 50 and @limit = 50, you'd get back results 50-100 (as ordered by the criteria specified in the Row_number over clause.

（我希望这是那种你要找的东西！）

(I hope this was the sort of thing you were looking for!)

编辑：这只会在SQL Server 2005开始工作 - 你没有提到你正在使用的版本。

This will only work in SQL Server 2005 onwards - you haven't mentioned which version you're using!

这篇关于SQL查询asp.net分页格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！