分页从SQL行 [英] Paginate rows from SQL

问题描述

我想完成我的书面方式新闻/博客ASP.net C＃应用程序，我陷入了如何在一个时间的过滤基于标签或关键字损益后返回仅10个项目。
这是一个例子我所期待的：

I am trying to finish writting my news/blog ASP.net C# application and I am getting stuck on how to return only 10 items at a time after filtering items based on tag or keyword. This is an example of what I am looking for:

1. 用户通过浏览查询SQL？标签=比萨


2. 后端发现等于查询字符串（送过来作为@参数）的所有标签。这部分我已经想通了。



3. 现在我希望它只是在一个时间从这个过滤语句返回10行。我很容易能与此做到这一点时，我并没有任何过滤出来：

1. User queries sql by browsing to ?tag=Pizza
2. Backend finds all tags that equal the query string (sent over as a @ parameter). This part I have figured out.

3. Now I want it to just return 10 rows at a time from this filtered statement. I was easily able to do this with this when I wasn't filtering anything out:

sqlCommand.CommandText = "SELECT * FROM (SELECT row_number() over(ORDER BY news_id) AS row_number, * FROM news) T WHERE row_number BETWEEN (SELECT rowcnt FROM sys.sysindexes WHERE (id = OBJECT_ID('news')) AND (indid < 2))-(@start+10) AND (SELECT rowcnt FROM sys.sysindexes  WHERE (id = OBJECT_ID('news')) AND (indid < 2))-(@start) ORDER BY news_id DESC";


sqlCommand.Parameters.Add("@start", SqlDbType.Int).Value = start;

我然后显示在页面的用户来查看更多选项的底部。 （这部分是容易，因为我只需要添加10的查询字符串？NUM =）。

I then display at the bottom of the page the user the option to view more. (This part is easy as I just need to add 10 to a query string of ?num=).

什么是去了解这一目标的最佳方式是什么？

What is the best way to go about this goal?

推荐答案

了解如何ROW_NUMBER数字结果是有点棘手在第一。

Understanding how row_number numbers the results is a little tricky at first.

它看起来像两件事情使这种复杂得多，它可以。 ROW_NUMBER函数必须在的 SELECT相同或外部SELECT 的比滤波正在发生的事情，否则，你的编号整个表，而不是编号的结果。您的查询编号表中的所有结果前行有限，因为ROW_NUMBER是在SELECT使用的是的在的SELECT与WHERE。

It looks like two things are making this more complicated than it could be. The row_number function must be in the same SELECT or an outer SELECT than the filtering is happening, otherwise you are numbering the whole table, rather than numbering the results. Your query numbered all the results in the table before the rows were limited because row_number was used in a SELECT that was inside the SELECT with the WHERE.

第二，它看起来像你打SYS.INDEXES获得的行数（这我不知道永远是准确的），以反向的行数范围时，你可以添加一个DESC到ROW_NUMBER函数。

Second, it looks like you are hitting sys.indexes to get the number of rows (which I am not sure is always accurate) to the the row number range in reverse when you can just add a DESC to the row_number function.

SELECT
    *
FROM
    (
        SELECT
            row_number() over(ORDER BY news_id DESC) AS row_number,
            *
        FROM
            news
        WHERE
            --user filter goes here
            news_cat = 'Pizza'
    ) AS T
WHERE
    row_number BETWEEN @start AND @start + 10

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