sql为当前行的下一行或上一行提取一行 [英] sql pulling a row for next or previous row of a current row
本文介绍了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 |
+------+---------------+---------------------+
这篇关于sql为当前行的下一行或上一行提取一行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文