[大数据]mysql-获取当前记录的上一条和下一条数据

# 说明：雪花算法的组成：  时间戳+机器id+序列号 ，所以可以认为他是递增的


#
select id , title, lag(id,1)  over
     (order by create_at) as lagId,lead(id,1)  over
     (order by create_at) as leadId  from  ec_message   ;



# last 1460528373946335233 next 1460528876793053186
select id , title , lastId , nextId from (
              select id , title, lag(id,1)  over
     (order by id) as lastId,lead(id,1)  over
     (order by id) as nextId  from  ec_message where deleted = 0
                  ) as Temp where id  = 1460528876759498754 ;



#② 因为 雪花算法 ： 时间戳+机器id+递增序列号 组成，所以 用这个思路
# last 1460528373946335233 next 1460528876793053186
select id, title,
       (select id as lastId  from ec_message
#        order by id desc limit 1 就可以 取得 最大值效果 类似于max
where deleted = 0 and sign(1460528876759498754-id) >0 order by id desc limit 1 ) as lastId,
       (select id as lastId  from ec_message
where deleted = 0 and sign(id-1460528876759498754) >0 order by id asc limit 1 ) as nextId
from ec_message
where deleted = 0
  and id = 1460528876759498754;


# last 1460528373946335233 next 1460528876793053186
select id, title,
       (select max(id) as lastId  from ec_message
#        order by id desc limit 1 就可以 取得 最大值效果 类似于max
where deleted = 0 and sign(1460528876759498754-id) >0  ) as lastId,
       (select min(id) as nextId  from ec_message
where deleted = 0 and sign(id-1460528876759498754) >0  ) as nextId
from ec_message
where deleted = 0
  and id = 1460528876759498754;


# 大于 目标id 的最小id，小于目标id 的最大id
#  将数据分成三部分 ：小于目标id的，等于目标id的，大于目标id的，然后取最大，最小的id
#  说明：max(),min() 是分组后的最大最小值，select max(id) from table ，是最大的一个分组
select case
           when sign(id - 1460528876759498754) < 0 then max(id)
           when sign(id - 1460528876759498754) = 0 then id
           when sign(id - 1460528876759498754) > 0 then min(id)
           end as id

from ec_message
where deleted = 0

group by sign(id - 1460528876759498754)
order by id;



select * from ec_message where id in
(select
case
when SIGN(id-1460528876759498754)>0 THEN MIN(id)
when SIGN(id-1460528876759498754)<0 THEN MAX(id)
ELSE id
end
from ec_message
where deleted = 0
# 分组的标准
GROUP BY SIGN(id-1460528876759498754)
ORDER BY SIGN(id-1460528876759498754)
)
ORDER BY id