组合多个mysql查询以将结果作为单行获取

问题描述：

This is my table : question

id   | name
-----+-----
 2   | name 1
 3   | name 2 
 4   | name 3
 5   | name 4
 10  | name 5 
 20  | name 6
 21  | name 7

I have an id say 5, I need to get the previous and next rows, so if id is 5 I need to get 4, 5 and 10

Now I am using these queries

select * from question where id = (select max(id) from question where id < 5);
select * from question where id = (select min(id) from question where id > 5);

What I am looking is for a single query (if its possible). Some thing like

SELECT xx AS prevId, id, yy AS nextId FROM questions WHERE .......

If there is not previous id or next id it should be 0 or -1

For example
id: 5
Return : 4, 5, 10

id:2
Return : -1, 2, 3

id: 21
Return 20, 21, -1

I know how to do this with multiple queries and with some if conditions in PHP, but I am looking for a pure mySQL approach if its possible.

答

Here are few examples, someone else can tell which one is more efficient.

Query 1

Select
  (select id from question where id<5 order by id desc limit 0,1) as prevId,
  id,
  (select id from question where id>5 order by id asc limit 0,1) as nextId
From question
Where id=5;

Query 2

Select
  (select max(id) from question where id<5) as prevId,
  id,
  (select min(id) from question where id>5) as nextId
From question 
Where id=5;

Ah, did not see -1 definition, needs some more hacking. Coalesce function takes 1..n arguments and returns first nonnull value.

Query 3

Select
  Coalesce( (select max(id) from question where id<21), -1) as prevId,
  id,
  Coalesce( (select min(id) from question where id>21), -1) as nextId
From question
Where id=21;

答

For next Record(ID):

SELECT * FROM `table_name` WHERE ID > $id ORDER BY ID LIMIT 1;

For prev Record(ID):

SELECT * FROM `table_name` WHERE ID < $id ORDER BY ID LIMIT 1;

Here $id is a variable in php which changes according to your desire

组合多个mysql查询以将结果作为单行获取

相关推荐