最有效的方式选择当前，上一个和下一个值的项目在SQLite - Most efficient way of selecting current, previous and next values of and item in SQLite

Most efficient way of selecting current, previous and next values of and item in SQLite

目前我有一个页面显示2图像在左侧和右侧与php和SQLite。

使用给定的图像id查询

该图像的文件
下一个图像的文件
next-next-image的链接
prev-prev-image的链接
到prev-image的链接(如果prev-prev不存在)

我已经尝试使用偏移量，但似乎整个查询失败，如果我的偏移量是越界，有任何方法来防止或捕获?

select id, file, findex
  from vfb
 where findex > (select findex from vfb where findex<4 and bookcode='BEFB'
                 order by findex desc limit 1 offset 1)
   and findex < (select findex from vfb where findex>4 and bookcode='BEFB'
                 order by findex asc limit 1 offset 1)
   and bookcode='BEFB'
 order by findex asc;

现在我正在使用select(select a where x<1) as a1, (select a where x=1) as b1, (select b where x=1) as c1结构，这是非常丑陋的，事实证明，我现在需要从查询(select a where x<1)中需要更多的信息。

所以，有没有办法缩短我的查询和或使用(select a,b where x<1) as a1,b2之类的东西?

完全丑陋的选择:

select 
(select findex from vfb where bookcode='BOOK' and findex<
    (select findex from vfb where bookcode='BOOK' and findex<100 order by findex desc limit 1) order by findex desc limit 1) as p2,
(select findex from vfb where bookcode='BOOK' and findex<100 order by findex desc limit 1) as p1,
(select file from vfb where bookcode='BOOK' and findex==100) as f1,
(select file from vfb where bookcode='BOOK' and findex>100 order by findex asc limit 1) as f2,
(select findex from vfb where bookcode='BOOK' and findex>
    (select findex from vfb where bookcode='BOOK' and findex>100 order by findex asc limit 1) order by findex asc limit 1) as n2;

结果:
98|99|BOOK_Page_104_Image_0001.png|BOOK_Page_105_Image_0001.png|102

我已经尝试使用偏移量，但似乎整个查询失败，如果我的偏移量是越界的，无论如何要防止或捕获?

不错的谜语，我只考虑了findex字段。
请尝试:

  SELECT t3.findex , t2.findex, t4.findex
    FROM vfb t1
    JOIN vfb t2 ON t1.findex > t2.findex
    JOIN vfb t3 ON t2.findex > t3.findex
    JOIN vfb t4 ON t1.findex < t4.findex
    WHERE
    t1.findex = 100
    AND t1.bookcode='BOOK' 
    AND t2.bookcode='BOOK' 
    AND t3.bookcode='BOOK'
    AND t4.bookcode='BOOK'
    ORDER by t3.findex desc, t2.findex desc, t4.findex asc
    LIMIT 1

如果上面是工作，请考虑添加索引在findex列。GL !

我最终做了一个PHP繁重的实现，因为这是我的工作。我有两个查询，最后是这样的:

$result = $db->query('select * from vfb where findex<=
(select findex from vfb where id=222)
and bookcode=(select bookcode from vfb where id=222)
group by page order by findex desc limit 3');

获取当前项及其下两个项。上述项目也可以这样做。然后使用PHP获得所需的值。