简单的问题,但我似乎找不到简单的解决方案:
给定以下数据,根据当前步骤编号获取上一步和下一步唯一十六进制ID的最有效方法是什么:
----+-------------+----------------
ID | UnqID | 斯蒂芬纳 姆 |
----+-------------+----------------
21 | 公元前 | 1 |
22 | 吉吉KL | 2 |
23 | 锰 | 3 |
24 | RsTuV | 4 |
25 | wXyZa | 5 |
----+-------------+----------------
例如:
让我们假设提供的步骤是3
,我如何获得2
和4
的唯一十六进制 ID?
谢谢!
编辑
这就是我目前正在使用的,但对我来说似乎不是那么有效??
SELECT
nxt.stepnum AS NextStep,
nxt.unqID AS NextUnqID,
prv.stepnum AS PrevStep,
prv.unqID AS PrevUnqID
FROM foo w,
(
SELECT stepnum, unqID FROM foo WHERE stepnum > 3 ORDER BY stepnum LIMIT 1
) AS nxt
,
(
SELECT stepnum, unqID FROM foo WHERE stepnum < 3 AND ORDER BY stepnum DESC LIMIT 1
) AS prv
WHERE w.stepnum = 3
我修改了你的查询。虽然它有点嵌套,但它应该包括 sql 引擎的非常简单的索引查找(理论上......
SELECT
nxt.stepnum AS NextStep,
nxt.unqID AS NextUnqID,
prv.stepnum AS PrevStep,
prv.unqID AS PrevUnqID
FROM (SELECT NULL) AS dummy -- dummy table to LEFT JOIN to
LEFT JOIN
( SELECT stepnum, unqID FROM foo
WHERE stepnum = (SELECT MIN(stepnum)
FROM foo
WHERE stepnum > 5 )
) AS nxt ON true
LEFT JOIN
( SELECT stepnum, unqID FROM foo
WHERE stepnum = (SELECT MAX(stepnum)
FROM foo
WHERE stepnum < 5 )
) AS prv ON true