Mysql选择下一步&上一页行不按ID排序 [英] Mysql Select Next & Prev row not order by id

问题描述

我有一个由NAME命令的查询，该查询返回的smt如下:

I have a query ordered by NAME that return smt like this:

 ID     NAME
2121927 AAA
2123589 AAB
2121050 AAC
2463926 BBB ---> known ID
2120595 CCC
2122831 DDD
2493055 EEE
2123583 EEF

我需要知道已知ID&&的下一个ID和上一个ID(如果存在).姓名 仅有1个查询怎么可能?

I need to know the next ID and the prev ID (if exists) of known ID && NAME How is it possible with only 1 query ?

推荐答案

  SELECT *,
         'next'
    FROM table
   WHERE `name` > 'BBB'
ORDER BY `name`
   LIMIT 1

UNION

  SELECT *,
         'previous'
    FROM table
   WHERE `name` < 'BBB'
ORDER BY `name` DESC
   LIMIT 1

如果您不知道特定的BBB name字段值-您可以将其替换为SELECT name FROM table WHERE id = 42之类的子查询，其中42是已知的ID值.

If you don't know particular BBB name field value - you could replace it with subquery like SELECT name FROM table WHERE id = 42, where 42 is the known ID value.

这篇关于Mysql选择下一步&上一页行不按ID排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！