选择一行及其周围的行 [英] Select a row and rows around it

问题描述

好吧，假设我有一张桌子，上面有照片.

Ok, let's say I have a table with photos.

我想做的是在页面上根据URI中的ID显示照片.下面是我要在附近的照片中添加10张缩略图的照片，而当前照片应位于缩略图的中间.

What I want to do is on a page display the photo based on the id in the URI. Bellow the photo I want to have 10 thumbnails of nearby photos and the current photo should be in the middle of the thumbnails.

到目前为止，这是我的查询(这只是一个示例，我使用7作为id):

Here's my query so far (this is just an example, I used 7 as id):

SELECT
    A.*
FROM
  (SELECT
       *
   FROM media
   WHERE id < 7
   ORDER BY id DESC
   LIMIT 0, 4
   UNION
   SELECT
       *
   FROM media
   WHERE id >= 7
   ORDER BY id ASC
   LIMIT 0, 6
  ) as A
ORDER BY A.id

但是我得到这个错误:

#1221 - Incorrect usage of UNION and ORDER BY

推荐答案

对于UNION查询，只能定义一个ORDER BY子句.使用UNION还是UNION ALL都没有关系. MySQL确实在UNION查询的某些部分上支持LIMIT子句，但是如果没有定义顺序的能力，它就相对没有用.

Only one ORDER BY clause can be defined for a UNION'd query. It doesn't matter if you use UNION or UNION ALL. MySQL does support the LIMIT clause on portions of a UNION'd query, but it's relatively useless without the ability to define the order.

MySQL还缺少排名功能，您需要使用排名功能来处理数据中的空白(由于条目被删除而丢失).唯一的选择是在SELECT语句中使用递增变量:

MySQL also lacks ranking functions, which you need to deal with gaps in the data (missing due to entries being deleted). The only alternative is to use an incrementing variable in the SELECT statement:

SELECT t.id, 
       @rownum := @rownum+1 as rownum 
  FROM MEDIA t, (SELECT @rownum := 0) r

现在我们可以获得行的连续编号列表，因此我们可以使用:

Now we can get a consecutively numbered list of the rows, so we can use:

WHERE rownum BETWEEN @midpoint - ROUND(@midpoint/2) 
                 AND @midpoint - ROUND(@midpoint/2) +@upperlimit

使用7作为@midpoint的值，@midpoint - ROUND(@midpoint/2)返回值4.要总共获得10行，请将@upperlimit值设置为10.这是完整的查询:

Using 7 as the value for @midpoint, @midpoint - ROUND(@midpoint/2) returns a value of 4. To get 10 rows in total, set the @upperlimit value to 10. Here's the full query:

SELECT x.* 
  FROM (SELECT t.id, 
               @rownum := @rownum+1 as rownum 
          FROM MEDIA t, 
               (SELECT @rownum := 0) r) x
 WHERE x.rownum BETWEEN @midpoint - ROUND(@midpoint/2) AND @midpoint - ROUND(@midpoint/2) + @upperlimit

但是，如果您仍然想使用LIMIT，则可以使用:

But if you still want to use LIMIT, you can use:

  SELECT x.* 
    FROM (SELECT t.id, 
                 @rownum := @rownum+1 as rownum 
            FROM MEDIA t, 
                 (SELECT @rownum := 0) r) x
   WHERE x.rownum >= @midpoint - ROUND(@midpoint/2)
ORDER BY x.id ASC
   LIMIT 10

这篇关于选择一行及其周围的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！