如何在mysql中获取下一行和上一行？
如何在mysql中获取下一行和上一行？
提问于 2018-09-14 08:25:13
我有一张桌子employees as，
id name startDate endDate
1 John 2013-8-9 2020-10-30
5 Nick 2011-6-7 2020-10-2
7 Pick 2012-6-9 2019-10-12我有按开始日期排序的员工列表。现在单击一条记录，我还希望获得基于开始日期的下一行和上一行。与上面的例子一样，数组被排序为1、7和5，所以当我单击7时，我还需要将1作为前一个数组，将5作为下一个数组。你能帮我吗，我通过id得到了记录，检查了比id的startDate更晚的日期，然后我按ASC顺序由statDate订购，并将记录限制为1。
回答 2
Stack Overflow用户
发布于 2018-09-14 09:18:53
您可以尝试使用子查询来计算起始日期较小或起始日期相同但ID较小的行数。在此基础上，您可以通过减去或加1来选择此行的计数等于具有所需ID的行的计数以及上一行或下一行的计数。
SELECT x.id,
x.name,
x.startdate,
x.end
FROM (SELECT t1.id,
t1.name,
t1.startdate,
t1.enddate,
(SELECT count(startdate)
FROM elbat t2
WHERE t2.startdate < t1.startdate
OR t2.startdate = t1.startdate
AND t2.id < t1.id) rn
FROM elbat t1
UNION ALL
SELECT NULL,
NULL,
NULL,
NULL,
-1
FROM dual
WHERE (SELECT (SELECT count(startdate)
FROM elbat t2
WHERE t2.startdate < t1.startdate
OR t2.startdate = t1.startdate
AND t2.id < t1.id)
FROM elbat t1
WHERE t1.id = ?) = 0
UNION ALL
SELECT NULL,
NULL,
NULL,
NULL,
(SELECT count(startdate)
FROM elbat t1) + 1
FROM dual
WHERE (SELECT (SELECT count(startdate)
FROM elbat t2
WHERE t2.startdate < t1.startdate
OR t2.startdate = t1.startdate
AND t2.id < t1.id)
FROM elbat t1
WHERE t1.id = ?) = (SELECT count(startdate)
FROM elbat t1)) x
WHERE x.rn IN ((SELECT (SELECT count(startdate)
FROM elbat t2
WHERE t2.startdate < t1.startdate
OR t2.startdate = t1.startdate
AND t2.id < t1.id)
FROM elbat t1
WHERE t1.id = ?) - 1,
(SELECT (SELECT count(startdate)
FROM elbat t2
WHERE t2.startdate < t1.startdate
OR t2.startdate = t1.startdate
AND t2.id < t1.id)
FROM elbat t1
WHERE t1.id = ?),
(SELECT (SELECT count(startdate)
FROM elbat t2
WHERE t2.startdate < t1.startdate
OR t2.startdate = t1.startdate
AND t2.id < t1.id)
FROM elbat t1
WHERE t1.id = ?) + 1)
ORDER BY x.rn;未测试，因为没有提供足够的DDL或DML！
将?替换为您要查询的ID。如果您不需要或不想要NULL行，如果没有前一行或下一行，请删除UNION ALLed的子查询。说到NULLs:按照目前的情况，它不能在开始日期为空的行上工作。
Stack Overflow用户
发布于 2018-09-14 09:29:23
对于"previous"，您可以使用：
select e.*
from employees e
where e.startDate > (select e2.startDate from employees e2 where e2.id = 7)
order by e.StartDate asc
limit 1;“下一步”：
select e.*
from employees e
where e.startDate < (select e2.startDate from employees e2 where e2.id = 7)
order by e.StartDate desc
limit 1;如果两者都需要，您可以将它们一起使用union all。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/52323399
复制相关文章
相似问题
腾讯云开发者
