SQL选择“窗口".围绕特定行 [英] SQL Selecting "Window" Around Particular Row

问题描述

很有可能以前曾提出过这样的问题，但我想不出要搜索的术语.

It's quite possible a question like this has been asked before, but I can't think of the terms to search for.

我正在使用照片库应用程序，并且想显示9个缩略图，以显示当前照片的上下文(在3x3网格中，当前照片位于中间，除非当前照片在前4个中显示照片，在这种情况下，例如，如果当前照片是第二张，我想选择照片1至9).例如，给定一个相册，其中包含具有ID的照片列表:

I'm working on a photo gallery application, and want to display 9 thumbnails showing the context of the current photo being shown (in a 3x3 grid with the current photo in the centre, unless the current photo is in the first 4 photos being shown, in which case if e.g. if the current photo is the 2nd I want to select photos 1 through 9). For example, given an album containing the list of photos with ids:

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

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

如果当前照片是19张，我还要查看:

If the current photo is 19, I want to also view:

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

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

如果当前照片为5张，我还要查看:

If the current photo is 5, I want to also view:

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

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

我一直在考虑以下方面的事情:

I've been thinking of something along the lines of:

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

，但是在id是非顺序的ID(如上面的示例)或当前照片之前的照片不足的情况下，这是行不通的.

but this doesn't work in the case where the ids are non-sequential (as in the example above), or for the case where there are insufficient photos before the currentphoto.

有什么想法吗?

谢谢

Dom

p.s.如果有任何不清楚的地方，请发表评论，我将澄清这个问题.如果有人能想到一个更有用的标题来帮助其他人将来找到这个问题，那么也请发表评论.

p.s. Please leave a comment if anything is unclear, and I'll clarify the question. If anyone can think of a more useful title to help other people find this question in future, then please comment too.

推荐答案

可能只使用UNION，然后在显示结果的过程代码中修剪掉多余的结果(因为这将在非边缘情况):

Probably could just use a UNION, and then trim off the extra results in the procedural code that displays the results (as this will return 20 rows in the non-edge cases):

(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 的建议，将UNION两侧的限制增加到10个.

Increased limit to 10 on both sides of the UNION, as suggested by le dorfier.

如Dominic所建议的那样，经过修改以更好地反映最终实现.

EDIT 2: Modified to better reflect final implementation, as suggested by Dominic.

这篇关于SQL选择“窗口".围绕特定行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！