mysql查询上一条和下一条信息

MySQL中查询已知记录的上一条和下一条记录

设MySQL中有一个形如下表的数据表,表名为test: 
+------+------+---------------+ 
| ID     | No     | Other                 | 
+------+------+---------------+ 
|       1 |       1 | Some others 1 | 
|       2 |       2 | Some others 2 | 
|       3 |       3 | Some others 3 | 
|     10 |       4 | Some others 4 | 
|     15 |       5 | Some others 5 | 
|     16 |       6 | Some others 6 | 
|     19 |       7 | Some others 7 | 
+------+------+---------------+ 

其中,"ID"为每条记录的唯一ID(一般为自增字段),"No"为每条记录在表中对应的位置(为直观起见),"Other"为表中的其他信息(可为N个字段). 

现在我们要查找"ID=3"的记录的"上一条"和"下一条"记录. 

1.仅列出字段"ID"的值: 
SELECT CASE WHEN SIGN(ID - 3) > 0 THEN 'Next' ELSE 'Prev' END AS DIR, CASE WHEN SIGN(ID - 3) > 0 THEN MIN(ID) WHEN SIGN(ID - 3) < 0 THEN MAX(ID) END AS ID FROM test WHERE ID <> 3 GROUP BY SIGN(ID - 3) ORDER BY SIGN(ID - 3); 


//SELECT id FROM 表名 WHERE id IN (SELECT CASE WHEN SIGN(id - "$当前id值") > 0 THEN MIN(id) WHEN SIGN(id - ". $当前id值.") < 0 THEN MAX(id) END AS id FROM mdm_message WHERE id <> ". $当前id值." GROUP BY SIGN(id - ". $当前id值.") ORDER BY SIGN(id - ". $当前id值.")) ORDER BY id ASC";

// $re=mysql_query($sql);

        while($row=mysql_fetch_row($re))

        {

       echo $row[0];//上一条id和下一条id

        }

结果: 
+------+------+ 
| DIR | ID   | 
+------+------+ 
| PREV |    2 | 
| NEXT |   10 | 
+------+------+ 

2.列出所有字段值:  
SELECT * FROM Test WHERE ID IN (SELECT CASE WHEN SIGN(ID - 3) > 0 THEN MIN(ID) WHEN SIGN(ID - 3) < 0 THEN MAX(ID) END AS ID FROM test WHERE ID <> 3 GROUP BY SIGN(ID - 3) ORDER BY SIGN(ID - 3)) ORDER BY ID ASC; 

结果: 
+------+------+---------------+ 
| ID   | No   | Other         | 
+------+------+---------------+ 
|    2 |    2 | Some others 2 | 
|   10 |    4 | Some others 4 | 
+------+------+---------------+ 

#Mysql