sql为当前行的下一行或上一行提取一行

本文介绍了sql为当前行的下一行或上一行提取一行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 id | photo title | created_date XEi43 | my family | 2009 08 04 dDls | friends group | 2009 08 05 32kJ | beautiful place | 2009 08 06 EOIk | working late | 2009 08 07

说我的ID是32kJ.我将如何获得下一行或上一行?

Say I have the id 32kJ. How would I get the next row or the previous one?

推荐答案

这是我用来查找上一个/下一个记录的方法.您表中的任何列都可以用作排序列，并且不需要联接或讨厌的骇客:

This is what I use for finding previous/next records. Any column in your table can be used as the sort column, and no joins or nasty hacks are required:

下一条记录(日期大于当前记录):

Next record (date greater than current record):

SELECT id, title, MIN(created) AS created_date FROM photo WHERE created > (SELECT created FROM photo WHERE id = '32kJ') GROUP BY created ORDER BY created ASC LIMIT 1;

上一个记录(日期小于当前记录)

Previous record (date less than current record):

SELECT id, title, MAX(created) AS created_date FROM photo WHERE created < (SELECT created FROM photo WHERE id = '32kJ') GROUP BY created ORDER BY created DESC LIMIT 1;

示例:

CREATE TABLE `photo` ( `id` VARCHAR(5) NOT NULL, `title` VARCHAR(255) NOT NULL, `created` DATETIME NOT NULL, INDEX `created` (`created` ASC), PRIMARY KEY (`id`) ) ENGINE = InnoDB; INSERT INTO `photo` (`id`, `title`, `created`) VALUES ('XEi43', 'my family', '2009-08-04'); INSERT INTO `photo` (`id`, `title`, `created`) VALUES ('dDls', 'friends group', '2009-08-05'); INSERT INTO `photo` (`id`, `title`, `created`) VALUES ('32kJ', 'beautiful place', '2009-08-06'); INSERT INTO `photo` (`id`, `title`, `created`) VALUES ('EOIk', 'working late', '2009-08-07'); SELECT * FROM photo ORDER BY created; +-------+-----------------+---------------------+ | id | title | created | +-------+-----------------+---------------------+ | XEi43 | my family | 2009-08-04 00:00:00 | | dDls | friends group | 2009-08-05 00:00:00 | | 32kJ | beautiful place | 2009-08-06 00:00:00 | | EOIk | working late | 2009-08-07 00:00:00 | +-------+-----------------+---------------------+ SELECT id, title, MIN(created) AS next_date FROM photo WHERE created > (SELECT created FROM photo WHERE id = '32kJ') GROUP BY created ORDER BY created ASC LIMIT 1; +------+--------------+---------------------+ | id | title | next_date | +------+--------------+---------------------+ | EOIk | working late | 2009-08-07 00:00:00 | +------+--------------+---------------------+ SELECT id, title, MAX(created) AS prev_date FROM photo WHERE created < (SELECT created FROM photo WHERE id = '32kJ') GROUP BY created ORDER BY created DESC LIMIT 1; +------+---------------+---------------------+ | id | title | prev_date | +------+---------------+---------------------+ | dDls | friends group | 2009-08-05 00:00:00 | +------+---------------+---------------------+