SQL Server分页查询 [英] SQL Server paging query

问题描述

Urggggg！我已经为此苦苦挣扎很长时间了！我可以很轻松地用MySQL做到这一点，但不能用SQL Server:(

Urggggg! I've been struggling with this for a long time! I can do it with MySQL so easy but not with SQL Server :(

这里是简化表，应该将它们全部连接在一起.

Here are the simplified tables which should be joined all together.

通过使用内部联接语法将所有这些组合在一起，我必须编写一个查询以供将来使用(btw，PHP)进行分页.假设我需要在2012年10月1日至2012年10月30日之间拍摄照片的所有ID，名称和日期信息，每页20个人.

Combining all of them by using inner join syntax, I have to write a query to use for paging in the future (btw, PHP). Let's say I need all ID, Name, and Date info which a picture is taken between 2012-10-01 and 2012-10-30.... and 20 people per page.

在这里实现目标最简单的查询是什么? (我尝试过NOT IN ..，但是它是如此的笨拙，因为我不习惯"NOT IN"之类的东西...)

What would be the easiest query to achieve the goal here? (I tried NOT IN.. but it was so buggy cuz I'm not used to 'NOT IN' stuff...)

您可以忽略性能速度.

谢谢！

推荐答案

这是我在SQL Server 2005+中的处理方式:

This is how I would do it in SQL Server 2005+:

SELECT ID, Name, Photo, CreatedDate, rowNum, (rowNum / 20) + 1 as pageNum
FROM (
    SELECT a.ID, a.Name, b.Photo, c.Created_Date
       , Row_Number() OVER (ORDER BY c.Created_Date ASC) as rowNum
    FROM a
       JOIN b ON a.ID = b.ID
       JOIN c ON c.photo = b.photo
    WHERE c.Created_Date BETWEEN '2012-01-1' AND '2012-10-30'
) x
WHERE (rowNum / 20) + 1 = 1

请注意，我正在使用一些整数除法来计算页数.

Note that I'm using a little integer division trickery to calculate page number.

由于2005年之前的版本没有row_number()，因此我将使用带有标识列的中间表:

Since pre-2005 sadly doesn't have row_number(), I'd use an intermediate table with an identity column:

    SELECT a.ID, a.Name, b.Photo, c.Created_Date
       , identity(int,1,1) as rowNum
    INTO t
    FROM a
       JOIN b ON a.ID = b.ID
       JOIN c ON c.photo = b.photo
    WHERE c.Created_Date BETWEEN '2012-01-1' AND '2012-10-30'
    ORDER BY c.Created_Date ASC
    GO

    ALTER TABLE t ADD pageNum AS rowNum / 20
    GO

    SELECT ID, Name, Photo, Created_Date, rowNum
    FROM t
    WHERE pageNum = 1 

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