SQL在特定行周围选择"Window“

Question

像这样的问题以前很可能已经被问过了，但我想不出要搜索的词。

我正在做一个相册应用程序，想要显示9个缩略图，显示当前照片的上下文(在3x3网格中，当前照片在中心，除非当前照片在显示的前4张照片中，在这种情况下，如果当前照片是第2张，我想选择照片1到9)。例如，给定一个包含带有ids的照片列表的相册：

1、5、9、12、13、18、19、20、21、22、23、25、26

如果当前照片是19，我还想查看：

9、12、13、18、19、20、21、22、23

如果当前照片是5，我还想查看：

1、5、9、12、13、18、19、20、21

我一直在想这样的事情：

SELECT *
FROM photos
WHERE ABS(id - currentphoto) < 5
ORDER BY id ASC 
LIMIT 25

但这在in不连续的情况下不起作用(如上面的示例)，或者在当前照片之前没有足够的照片。

有什么想法吗？

谢谢,

Dom

附注：如果有任何不清楚的地方，请留下评论，我会澄清这个问题。如果任何人能想出一个更有用的标题来帮助其他人找到这个问题，那么也请发表评论。

Answer 1

可能只需要使用一个UNION，然后在显示结果的过程代码中修剪掉额外的结果(因为这将在非边缘情况下返回20行)：

(SELECT 
     * 
FROM photos
   WHERE ID < #current_id#
   ORDER BY ID DESC LIMIT 10)
UNION
  (SELECT *
   FROM photos
   WHERE ID >= #current_id#
   ORDER BY ID ASC LIMIT 10)
ORDER BY ID ASC

编辑:根据le dorfier的建议，联盟两侧的限制增加到10个。

编辑2:根据Dominic的建议进行修改，以更好地反映最终实现。

Answer 2

如果您使用的是SQL Server，则可以使用row_number()函数为您提供行顺序索引，并执行以下操作：

declare @selected_photo integer;
set @selected_photo = 5;

declare @buffer_size integer;
set @buffer_size = 2;

select
   ph.rownum,
   ph.id
from
   (select row_number() over (order by Id) as rownum, * from Photos) as ph
where
   ph.rownum between case
                         when @selected_photo - @buffer_size < 1 then 1
                         else @selected_photo - @buffer_size
                      end
                      and @selected_photo + @buffer_size

编辑:这里有一篇关于在MySQL中模拟row_number()函数的文章，把它和这个结合起来可能会得到你需要的东西--我会试一试，但在工作中我手头没有MySQL db。:-)

http://www.xaprb.com/blog/2006/12/02/how-to-number-rows-in-mysql/

Answer 3

这是标准的“行排序”问题...如果您的数据库具有rowId功能，则可以使用该功能，否则您需要一个子查询来计算id小于当前行的id的行数...如下所示：

-- asssuming @Id是“中间”中id的值。

 Select *  From Photos P
 Where (Select Count(*) From Photos
         Where id <= P.Id)
     Between (Select Count(*) From Photos
              Where id < @Id) - 4
        And  (Select Count(*) From Photos
              Where id < @Id) + 4

当评论引发专辑问题时，您可能希望向每个子查询添加专辑谓词

   Select *  From Photos P
   Where (Select Count(*) From Photos
          Where album = @album
            And id <= P.Id)
     Between (Select Case When Count(*) < 4 
                      Then 4 Else Count(*) End
              From Photos
              Where album = @album
                 And id < @Id) - 4
        And  (Select Case When Count(*) < 4 
                      Then 4 Else Count(*) End
              From Photos
              Where album = @album
                  And id < @Id) + 4