ETJava Beta | Java    注册   登录
  • MYSQL 获取临近的两条数据

    发表于 2024-01-06 23:22:23     阅读(239)     博客类别:MySQL

    描述: 获取临近的两条数据 例如 id=3 那么获取到的数据应是id<3,id=3,id>3 这样三条数据

    例如下列表格数据 获取id=2的数据时 应查询出来的是id=1,id=2,id=3的这三条数据的集合

    id title
    1 title1
    2 title2
    3 title3
    SELECT t1.ID,  t2.ID AS next_id,t2.title AS nextTitle, t3.ID AS prev_id,t3.title AS prevTitle
    FROM tableName AS t1
    LEFT JOIN tableName  AS t2 ON t1.ID + 1 = t2.ID
    LEFT JOIN tableName  AS t3 ON t1.ID - 1 = t3.ID
    WHERE t1.ID = 2;

    查询到的结果应为

    id next_id nextTitle prev_id prevTitle
    2 3 title3 1 title1