代码之家 › 专栏 › 技术社区 › Carra

从数据库获取消息1-20、21-40…。

sybase oracle sql-server sql

Carra · 技术社区 · 15 年前

我正在尝试建立一个邮箱,在这里我们可以将邮件分组到X中。如果将x设置为20,您将在第一页看到消息1-20,打开第二页将显示消息21-40等。

如何有效地查询?我能想到的最好办法是:

select top 20 * 
from tbl_messages
where 
tnr_id not in
(
    select top 40 tnr_id   â20/40/60/80/â¦
    from tbl_messages
    order by dt_made desc, tnr_id desc
)
order by dt_made desc, tnr_id desc

有更有效的方法吗?使用的数据库有SQL Server、Oracle和Sybase。

3 回复 | 直到 15 年前

Quassnoi 15 年前

在 Oracle :

SELECT  *
FROM    (
        SELECT  t.*, rownum AS rn
        FROM    tbl_messages t
        ORDER BY
                dt_made DESC, tnr_id DESC
        )
WHERE   rn > 40
        AND rownum <= 20

在 SQL Server 2005 及以上:

DECLARE @start INT
DECLARE @pagesize INT
SET @start = 40
SET @pagesize = 20  

SELECT  *
FROM    (
        SELECT  TOP (@start + @pagesize)
                t.*, ROW_NUMBER() OVER (ORDER BY dt_made DESC, tnr_id DESC) AS rn
        FROM    tbl_messages t
        ORDER BY
                dt_made DESC, tnr_id DESC
        ) q
WHERE   rn > @start

ROW_NUMBER 得到支持 甲骨文公司 同样,但是由于实现的细节比 rownum .

有关性能比较,请参阅我的博客中的这篇文章:

Oracle: ROW_NUMBER vs ROWNUM

更新:

如果您可以容忍并发更新导致的某些差异,那么您可以记住客户端当前页面上的最后一条记录,并使用它更快地获得下一个结果:

SELECT  TOP 20 *
FROM    tbl_messages t
WHERE   dt_made <= @last_dt_made
        AND NOT (dt_made = @last_dt_made AND tnr_id >= @last_tnr_id)
ORDER BY
        dt_made DESC, tnr_id DESC

KM. 15 年前

对于SQL Server,请尝试以下操作:

--set up a table to page through:
DECLARE @TestTable  table (TableID int )

INSERT INTO @TestTable (TableID)
SELECT TOP 1000 row_number() over(order by t1.number) as N
FROM master..spt_values t1 
    CROSS JOIN master..spt_values t2

--set page location and size
DECLARE @Start  int
DECLARE @Size   int

SET @Start=40
SET @Size=20

--return data for that page:
SELECT
    *
    FROM (SELECT 
              v.*, ROW_NUMBER() OVER (ORDER BY TableID) AS RowNumber
              FROM  @TestTable  v
         ) dt
    WHERE RowNumber>=@Start AND RowNumber<@Start+@Size

输出:

TableID     RowNumber
----------- --------------------
40          40
41          41
42          42
43          43
44          44
45          45
46          46
47          47
48          48
49          49
50          50
51          51
52          52
53          53
54          54
55          55
56          56
57          57
58          58
59          59

(20 row(s) affected)

Matt Whitfield 15 年前

对于SQL Server-也许更灵活一点,允许您指定每页的页数和消息数。

DECLARE @pageNumber int, @messagesPerPage int
SET @pageNumber = 3
SET @messagesPerPage = 20

SELECT *
  FROM (
        SELECT t.*, ROW_NUMBER() OVER (ORDER BY [dt_made] DESC, [tnr_id] DESC) AS __RN
          FROM tbl_messages t
       ) iDat
 WHERE __RN BETWEEN (@pageNumber - 1) * @messagesPerPage AND @pageNumber * @messagesPerPage
 ORDER BY dt_made DESC, tnr_id DESC