mysql - 如何创建仅显示一条特定记录之前和之后的 mysql 查询？

Question

我有一个按字母顺序排列的查询

select id, color_name from colors order by color_name

输出

| 4 | blue| 1 | red| 2 | violet| 3 | yellow

我想查询特定记录的前后记录。示例:红色

期望的输出:

| 4 | blue| 1 | red| 2 | violet

谢谢。

Answer 1

尝试使用 UNION:

SELECT  A.id, A.color_name FROM  (
   (
      SELECT  id, color_name FROM colors
      WHERE color_name <= 'red'
      ORDER BY color_name DESC
      LIMIT 2
   )
  UNION
   (
      SELECT id, color_name FROM colors
      WHERE color_name > 'red'
      ORDER BY color_name ASC
      LIMIT 1
   )

 ) as A
ORDER BY A.color_name