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

说我的身份是32kJ.如何获得下一行或前一个？

解决方法

这是我用来查找上一个/下一个记录.表中的任何列都可以用作排序列,并且不需要连接或讨厌的黑客：

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

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;

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

SELECT id,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`,`created`) VALUES ('dDls','friends group','2009-08-05');
INSERT INTO `photo` (`id`,`created`) VALUES ('32kJ','beautiful place','2009-08-06');
INSERT INTO `photo` (`id`,`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,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,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为当前行的下一行或上一行拉一行

解决方法

猜你在找的MsSQL相关文章