MySQL sql为当前行的下一行或上一行拉一行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1259458/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
sql pulling a row for next or previous row of a current row
提问by Basit
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
Say I have the id 32kJ. How would I get the next row or the previous one?
说我有 id 32kJ。我如何获得下一行或上一行？
回答by Mike
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;
Example:
例子：
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 |
+------+---------------+---------------------+
回答by ObiWanKenobi
I realize that you are using MySQL, but just for reference, here is how you would do this using Oracle's analytic functions LEAD and LAG:
我知道您正在使用 MySQL，但仅供参考，以下是您如何使用 Oracle 的分析函数 LEAD 和 LAG 执行此操作：
select empno, ename, job,
lag(ename, 1) over (order by ename) as the_guy_above_me,
lead(ename, 2) over (order by ename) as the_guy_two_rows_below_me
from emp
order by ename
I guess there's a reason why Oracle costs money and MySQL is free... :-)
我想甲骨文要花钱，而 MySQL 是免费的，这是有原因的... :-)
This page shows you how to emulate analytic functions in MySQL.
此页面向您展示如何在 MySQL 中模拟分析函数。
回答by Jeremy Stein
Did you want the next/previous row by date? If so, you could do this:
您想要按日期显示下一行/上一行吗？如果是这样，你可以这样做：
select MyTable.*
from MyTable
join
(select id
from MyTable
where created_date < (select created_date from MyTable where id = '32kJ')
order by created_date desc, id desc
limit 1
) LimitedTable on LimitedTable.id = MyTable.fund_id;
回答by probably clueless
Using Mike'sMAX/MIN trick we can make previous\next jumps for all sorts of things. This msAccess example will return the previous close for every record in a stock market data table. Note: the '<=' is for weekends and holidays.
使用Mike 的MAX/MIN 技巧，我们可以为各种事情进行上一个/下一个跳转。此 msAccess 示例将返回股票市场数据表中每条记录的上一个收盘价。注意：'<=' 用于周末和节假日。
SELECT
tableName.Date,
tableName.Close,
(SELECT Close
FROM tableName
WHERE Date = (SELECT MAX(Date) FROM tableName
WHERE Date <= iJoined.yesterday)
) AS previousClose
FROM
(SELECT Date, DateAdd("d",-1, Date) AS yesterday FROM tableName)
AS iJoined
INNER JOIN
tableName ON tableName.Date=iJoined.Date;
...'yesterday' demonstrates using a function(Date-1) jump; we could have simply used...
...'yesterday' 演示使用函数( Date-1) 跳转；我们可以简单地使用...
(SELECT Date FROM tableName) AS iJoined
/* previous record */
(SELECT MAX(Date) FROM tableName WHERE Date < iJoined.Date)
/* next record */
(SELECT MIN(Date) FROM tableName WHERE Date > iJoined.Date)
The trick is we can previous\next # of whatever(s)with MAX\MIN and a jump function()
诀窍是，我们可以在以前的\下一＃什么（S）与MAX \ MIN和跳转功能（）
回答by Rahul More
I considered id as primary key in the table (and as "row number"), and used it to compare each record with the record before. The following code must work.
我将 id 视为表中的主键（以及“行号”），并用它来比较每条记录与之前的记录。以下代码必须工作。
CREATE SCHEMA temp
create table temp.emp (id integer,name varchar(50), salary varchar(50));
insert into temp.emp values(1,'a','25000');
insert into temp.emp values(2,'b','30000');
insert into temp.emp values(3,'c','35000');
insert into temp.emp values(4,'d','40000');
insert into temp.emp values(5,'e','45000');
insert into temp.emp values(6,'f','20000');
select * from temp.emp
SELECT
current.id, current.name, current.salary,
case
when current.id = 1 then current.salary
else
case
when current.salary > previous.salary then previous.salary
else current.salary
end
end
FROM
temp.emp AS current
LEFT OUTER JOIN temp.emp AS previous
ON current.id = previous.id + 1
回答by Tikeb
Horrible hack - I don't like this but might work..
可怕的黑客 - 我不喜欢这个，但可能会工作..
with yourresult as
(
select id, photo_title, created_date, ROW_NUMBER() over(order by created_date) as 'RowNum' from your_table
)
-- Previous
select * from yourresult where RowNum = ((select RowNum from yourresult where id = '32kJ') -1)
-- Next
select * from yourresult where RowNum = ((select RowNum from yourresult where id = '32kJ') +1)
That of any use?
那有什么用？
