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

相关文章推荐

慷慨大方的保温杯 · Mysql下使用mybatis查询，报jav ...· 1 年前 ·

多情的长颈鹿 · PermissionError: ...· 2 年前 ·

面冷心慈的熊猫 · Spring Session - ...· 2 年前 ·

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 个回答