SQL 查询跟踪问题

本文关键字：问题 跟踪 查询 SQL | 更新日期: 2023-09-27 17:56:17

好的，这是我的问题。

用户可以进入我的网站并一次检索 8 条记录，然后他/她可以选择加载更多记录。这 8 条记录可以通过传递到 proc 中的参数进行排序。现在，当我在前端获得这 8 条记录时，我有它们的 ID（尽管显然对用户隐藏），但他们的 ID 不按任何特定顺序排列，因为这些记录是按各种可能的东西排序的。

当他们单击"加载更多"时，我应该能够从数据库中获取接下来的 8 条记录，以与前 8 条相同的方式排序。

例如，"给我按年龄排序的前 8 条记录"。 -> 点击加载更多 -> 给我接下来的 8 条最旧的记录，而不给我看我刚刚看到的一次。

如何调用 proc 并确保不返回第一个结果集中的任何结果集？出于效率原因，我只想一次返回 8 条记录。

SELECT TOP 8
      m.message,
      m.votes,
      (geography::Point(@latitude, @longitude, 4326).STDistance(m.point)) * 0.000621371192237334 as distance,
      m.location,
      datediff(hour,m.timestamp, getdate()) as age,
      m.messageId,
      ml.voted,
      ml.flagged
    FROM
      tblMessages m
    left join tblIPMessageLink ml on m.messageid = ml.messageid
    WHERE
      m.timestamp >= DATEADD(day, DATEDIFF(day, 0, @date), 0)
      and
      m.timestamp < DATEADD(day, DATEDIFF(day, 0, @date), 1)
      ORDER BY
        CASE WHEN @sort = 'votes1' THEN m.votes END DESC,
        CASE WHEN @sort = 'votes2' THEN m.votes END ASC,
        CASE WHEN @sort = 'age1' THEN datediff(hour,m.timestamp, getdate()) END ASC,
        CASE WHEN @sort = 'age2' THEN datediff(hour,m.timestamp, getdate()) END DESC,
        CASE WHEN @sort = 'distance1' THEN (geography::Point(@latitude, @longitude, 4326).STDistance(m.point)) * 0.000621371192237334 END ASC,
        CASE WHEN @sort = 'distance2' THEN (geography::Point(@latitude, @longitude, 4326).STDistance(m.point)) * 0.000621371192237334 END DESC
      END

这是我目前的查询。如何更改它以使用分页？

SQL 查询跟踪问题

使用row_number

例

呼叫 1

;WITH cte AS(SELECT *,row_number() OVER( ORDER BY name) AS rows FROM sysobjects)
SELECT * FROM cte WHERE ROWS BETWEEN 1 AND 8
ORDER BY rows

呼叫 2

;WITH cte AS(SELECT *,row_number() OVER( ORDER BY name) AS rows FROM sysobjects)
SELECT * FROM cte WHERE ROWS BETWEEN 9 AND 16
ORDER BY rows

当然你想使用参数而不是硬编码数字，这样你可以重用查询，如果列可以任意排序，那么你可能需要使用动态 SQL

编辑，这是它应该的样子，您可能还想返回最大行号，以便您知道可以返回多少行您还可以使每页的行动态化，在这种情况下，它将是这样的

其中 @StartRow 和 （@StartRow + @RowsPerPage） 之间的行 -1

请务必阅读 SQL 2008 T-SQL 版本中的动态搜索条件，了解如何对其进行优化，以获得计划重用和更好的计划

无论如何，这是proc，当然未经测试，因为我不能在这里运行它

DECLARE @StartRow INT,@EndRow INT
--SELECT @StartRow =1, @EndRow = 8

;WITH cte AS (SELECT ROW_NUMBER() OVER (ORDER BY
        CASE WHEN @sort = 'votes1' THEN m.votes END DESC,
        CASE WHEN @sort = 'votes2' THEN m.votes END ASC,
        CASE WHEN @sort = 'age1' THEN datediff(hour,m.timestamp, getdate()) END ASC,
        CASE WHEN @sort = 'age2' THEN datediff(hour,m.timestamp, getdate()) END DESC,
        CASE WHEN @sort = 'distance1' THEN (geography::Point(@latitude, @longitude, 4326).STDistance(m.point)) * 0.000621371192237334 END ASC,
        CASE WHEN @sort = 'distance2' THEN (geography::Point(@latitude, @longitude, 4326).STDistance(m.point)) * 0.000621371192237334 END DESC
      END) AS rows 
      m.message,
      m.votes,
      (geography::Point(@latitude, @longitude, 4326).STDistance(m.point)) * 0.000621371192237334 as distance,
      m.location,
      datediff(hour,m.timestamp, getdate()) as age,
      m.messageId,
      ml.voted,
      ml.flagged
    FROM
      tblMessages m
    left join tblIPMessageLink ml on m.messageid = ml.messageid
    WHERE
      m.timestamp >= DATEADD(day, DATEDIFF(day, 0, @date), 0)
      and
      m.timestamp < DATEADD(day, DATEDIFF(day, 0, @date), 1)
      )

     SELECT * 
     FROM cte WHERE ROWS BETWEEN @StartRow AND @EndRow
    ORDER BY rows

David Hayden 有一篇关于分页的好文章。您只需要跟踪记录数和偏移量。

此外，您仍然需要在每次加载更多记录时合并和重新访问客户端上的记录

这是那篇文章中的SP

CREATE PROCEDURE dbo.ShowLog
    @PageIndex INT, 
    @PageSize INT 
AS
BEGIN 
WITH LogEntries AS ( 
SELECT ROW_NUMBER() OVER (ORDER BY Date DESC)
AS Row, Date, Description 
FROM LOG)
SELECT Date, Description
FROM LogEntries 
WHERE Row between 
(@PageIndex - 1) * @PageSize + 1 and @PageIndex*@PageSize

END
相关文章：