代码之家 › 专栏 › 技术社区 › Richard Knop

选择一行及其周围的行

sql mysql

Richard Knop · 技术社区 · 15 年前

好吧,假设我有一张有照片的桌子。

我要做的是在一个页面上根据URI中的ID显示照片。下面的照片我想有10个附近的照片的缩略图,当前的照片应该在缩略图的中间。

这是我目前为止的查询(这只是一个示例,我使用7作为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

7 回复 | 直到 10 年前

OMG Ponies 15 年前

只有一个 ORDER BY 子句可以定义为 UNION D查询。你用它没关系 联合 或 UNION ALL . mysql支持 LIMIT A部分条款 联合 'd查询,但是如果没有定义顺序的能力,它就相对无用了。

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

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

现在我们可以得到一个连续编号的行列表,因此我们可以使用:

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

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

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

但如果你还想用 极限 ,您可以使用:

  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

Marek Grzenkowicz 13 年前

我使用以下代码解决此问题:

SELECT  A.* FROM  (
   (
      SELECT  *  FROM gossips
      WHERE id < 7
      ORDER BY id DESC
      LIMIT 2
   )
  UNION
   (
      SELECT * FROM gossips
      WHERE id > 7
      ORDER BY id ASC
      LIMIT 2
   )

 ) as A
ORDER BY A.id

malonso 15 年前

我不相信你能在工会的不同部门有一个“订单依据”。你能不能这样做:

SELECT * FROM media where id >= 7 - 4 and id <= 7 + 4 ORDER BY id

Jonathan 15 年前

我同意马隆索建议的答案(+1),但如果你用id=1试试,你只会得到5个缩略图。我不知道你是否想要这种行为。如果你总是想要10个拇指,你可以尝试:

select top 10 * from media where id > 7 - 4

问题是select top依赖于数据库(在本例中是一个SQL Server子句)。其他数据库有类似的子句:

神谕:

SELECT *  media
FROM media
WHERE ROWNUM < 10
AND id > 7 - 4

MySQL:

SELECT * 
FROM media
WHERE id > 7 - 4
LIMIT 10

所以也许你可以用最后一个。

如果我们这样做,如果你想要最后10个拇指,我们也会有同样的问题。举例来说,如果我们有90个拇指,我们给出一个id=88…您可以通过添加或条件来解决它。在MySQL中,会出现如下情况:

SELECT * 
    FROM media
    WHERE id > 7 - 4
    OR (Id+5) > (select COUNT(1) from media)
    LIMIT 10

Will 15 年前

如果您愿意使用临时表,可以将原始查询分解为使用临时表。

SELECT
    *
FROM media
WHERE id < 7
ORDER BY id DESC
LIMIT 0, 4
INTO TEMP t1;

INSERT INTO t1
SELECT
   *
FROM media
WHERE id >= 7
ORDER BY id ASC
LIMIT 0, 6;

select * from t1 order by id;
drop table t1;

Mark Thornton 15 年前

尝试 联合所有 相反。联合要求服务器确保结果唯一,这与您的排序冲突。

humbleice 10 年前

我必须解决一个类似的问题,但是需要考虑这样的情况:我们总是得到相同数量的行,即使所需的行接近结果集的顶部或底部(即不完全在中间)。

这个解决方案是来自omg ponies响应的一个调整,但是rownum在所需行上最大化:

set @id = 7;
SELECT natSorted.id 
  FROM (
       SELECT gravitySorted.* FROM (
           SELECT Media.id, IF(id <= @id, @gravity := @gravity + 1, @gravity := @gravity - 1) AS gravity 
             FROM Media, (SELECT @gravity := 0) g
       ) AS gravitySorted ORDER BY gravity DESC LIMIT 10
  ) natSorted ORDER BY id;

下面是正在发生的事情的一个分解:

注意:在下面的示例中,我创建了一个包含20行的表,并删除了ID6和9,以确保IDS中的间隙不会影响结果。

首先,我们为每一行指定一个重心值,该值以您要查找的特定行为中心(在本例中,ID为7)。行越接近所需行,值越高:

SET @id = 7;
SELECT Media.id, IF(id <= @id, @gravity := @gravity + 1, @gravity := @gravity - 1) AS gravity 
  FROM Media, (SELECT @gravity := 0) g

+----+---------+
| id | gravity |
+----+---------+
|  1 |       1 |
|  2 |       2 |
|  3 |       3 |
|  4 |       4 |
|  5 |       5 |
|  7 |       6 |
|  8 |       5 |
| 10 |       4 |
| 11 |       3 |
| 12 |       2 |
| 13 |       1 |
| 14 |       0 |
| 15 |      -1 |
| 16 |      -2 |
| 17 |      -3 |
| 18 |      -4 |
| 19 |      -5 |
| 20 |      -6 |
| 21 |      -7 |
+----+---------+

接下来,我们根据重力值和所需行数的限制来排序所有结果:

SET @id = 7;
SELECT gravitySorted.* FROM (
    SELECT Media.id, IF(id <= @id, @gravity := @gravity + 1, @gravity := @gravity - 1) AS gravity 
      FROM Media, (SELECT @gravity := 0) g
) AS gravitySorted ORDER BY gravity DESC LIMIT 10

    +----+---------+
    | id | gravity |
    +----+---------+
    |  7 |       6 |
    |  5 |       5 |
    |  8 |       5 |
    |  4 |       4 |
    | 10 |       4 |
    |  3 |       3 |
    | 11 |       3 |
    |  2 |       2 |
    | 12 |       2 |
    |  1 |       1 |
    +----+---------+

此时,我们已经拥有了所有需要的ID,我们只需要将它们按原始顺序进行排序:

设置@ ID=7;
选择natsorted.id
从(
选择重力排序。*从(
选择media.id,如果(id<=@id,@gravity:=@gravity+1,@gravity:=@gravity-1)作为重力
从介质中,(选择@重力:=0)g
)按重力排序,按重力描述极限10排序
)按ID排序;

+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  7 |
|  8 |
| 10 |
| 11 |
| 12 |
+----+