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);

结果:

+------+------+

| 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 |

+------+------+---------------+