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