如何在mysql中获取下一行和上一行？

link之家

链接快照平台

输入网页链接，自动生成快照
标签化管理网页链接

相关文章推荐

直爽的棒棒糖 · 上海科技影都建设发展“十四五”规划· 2 月前 ·

可爱的伏特加 · 富士见二丁目交响乐团_百度百科· 10 月前 ·

讲道义的楼房 · 各地人民群众对就业促进法草案的意见（一）_中 ...· 1 年前 ·

鬼畜的水煮肉 · 第七届中国SKA暑期学校成功举办 ...· 1 年前 ·

不拘小节的仙人掌 · Manga - Page 1156 of ...· 1 年前 ·

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

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,
                    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;

select e.*
from employees e
where e.startDate > (select e2.startDate from employees e2 where e2.id = 7)

如何在mysql中获取下一行和上一行？ 内容来源于 Stack Overflow，遵循 CC BY-SA 4.0 许可协议进行翻译与使用。IT领域专用引擎提供翻译支持 腾讯云小微IT领域专用引擎提供翻译支持

2 个回答