mysql按字母字符选择下一个和上一个记录顺序

6ss1mwsb  于 2021-06-15  发布在  Mysql
关注(0)|答案(1)|浏览(432)

我有一个数据库表与多个公司配置文件,我想显示与上一个和下一个公司链接每个公司配置文件。如果当前公司名为“d”,则上一个公司将为“c”,下一个公司为“e”。请帮帮我。

current ->  select * from `sample` where id = 4; value is D

previous - > select * from `sample` where (need to fetch record with value c ) limit 1

next - > select * from `sample` where (need to fetch record with value e ) limit 1
7eumitmz

7eumitmz1#

你可以用窗口函数来实现 LAG() 以及 LEAD() .
如果要在一行中显示结果:

WITH cte AS (
  SELECT *, 
         LAG(id) OVER (ORDER BY value) prev_id,
         LEAD(id) OVER (ORDER BY value) next_id
  FROM tablename         
)
SELECT * 
FROM cte
WHERE value = 'd'

结果:

> id | value | prev_id | next_id
> -: | :---- | ------: | ------:
>  4 | d     |      11 |      12

或者如果需要两行:

WITH cte AS (
  SELECT *, 
         LAG(value) OVER (ORDER BY value) prev_value,
         LEAD(value) OVER (ORDER BY value) next_value
  FROM tablename         
)
SELECT id, value
FROM cte
WHERE 'd' IN (prev_value, next_value)

结果:

> id | value
> -: | :----
> 11 | c    
> 12 | e

请看演示。

相关问题