在查询之前和之后获取行 [英] Getting row before and after a query

问题描述

我目前有这个查询

SELECT  short_url,
        type,
        file_thumbnail,
        file_embed,
        media.id,
        user,
        media.file_url,
        votes,
        comments,
        GROUP_CONCAT(info.itemTime) AS time,
        info.title,
        media.time AS oldTime,
        media.title,
        info.topic,
        GROUP_CONCAT(votes) AS totalVotes,
        GROUP_CONCAT(votes) AS listVotes, 
        GROUP_CONCAT(comments) AS listComments, 
        GROUP_CONCAT(url) AS listSites 
from    info JOIN 
        media on info.mid=media.id 
WHERE   media.id='$id' 
GROUP BY    mid 
ORDER BY    media.id DESC 
LIMIT 0,1

我添加了一项新功能，可让您轻松导航到上一个或下一个项目.有没有一种简单的方法可以修改上面的查询，以便它提取上一行，当前行和下一行?还是只进行另一个查询会更容易?

I'm adding a new feature that lets you easily navigate to the previous or next item. Is there an easy way to modify the above query so that it pulls the prior row, current row and the next row? Or would it be easier to just do another query?

推荐答案

您可以使用子查询获取previos和下一条记录的ID:

You can use subqueries to get the id of the previos and next record:

...
where media.id in (
  '$id',
  (select max(id) from media where id < '$id'),
  (select min(id) from media where id > '$id')
)
...

这篇关于在查询之前和之后获取行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！