MySQL - 下一个/以前的ID与自行车 [英] MySQL - next / previous ID wih cycling

问题描述

我需要使用骑车创建上一个/下一个功能。我当前的解决方案，如果我选择 MAX（ID）给下一个 NULL 。

I need to create previous / next functionality with cycling. My current solution, If I select MAX(ID) gives next as NULL.

如何最有效地获取 MIN（ID）而不是 NULL （反之亦然）。当然，我可以使用 IF 或者只是创建第二个查询，如果我的结果是 NULL ，但我想知道是否更好的解决方案。

How can I most efficiently get MIN(ID) instead of NULL(and vice versa for other direction). Of course, I can use IF or just create second query if my result is NULL, but I wonder if there is better solution.

我的表有三列

ex：

ID     foto    like

3        A      0
4        B      0
5        C      0
10       D      0

如果我选择ID 4，则为5，prev为3 //这个解决方案我有

If I select ID 4 next is 5, prev is 3 //this solution I have

对于ID 3，下一个是4，上一个是10 //这是我想要的

For ID 3, next is 4, previous is 10 //thats what I want

对于上一个/下一个我使用此查询

For previous / next I use this query

(
    SELECT MIN(`ID`) AS id 
    FROM fotos 
    WHERE `ID` > '$this->id'
    ORDER BY `like` DESC
)
UNION
(
    SELECT MAX(`ID`) AS id 
    FROM fotos 
    WHERE `ID` < '$this->id'                                            
    ORDER BY `like` DESC
)

$ this-> id 对于当前 foto 的下一个和上一个ID，可以使用foto

$this->id is actually selected and displayed foto

推荐答案

像这样

SELECT COALESCE((SELECT id FROM `foto` WHERE id<'$this->id' ORDER BY DESC id LIMIT 1),
       (SELECT  MAX(id) FROM `foto`))  AS `prev`,
       COALESCE((SELECT id FROM `foto` WHERE id>'$this->id' ORDER BY id LIMIT 1 ),
       (SELECT MIN(id) FROM `foto`)) AS `next`

希望它适合你的需要

这篇关于MySQL - 下一个/以前的ID与自行车的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！