doubaben7394 2017-11-26 13:22
浏览 135
已采纳

从当前id SQL PHP获取两个上一行和下一行数据

i have a table with database and i want get two data before current id and get two data after current id.

primary_key     id
1               345
2               356
3               400
4               102
5               210
6               190

Case:

  • If current id defined 400, the results as before is 356 & 345 and after is 102 & 210
  • If current id defined 210, the results as before is 102 & 400 and after is 190
  • If current id defined 356, the results as before is 345 and after is 400 & 102
  • If current id defined 345, the results as before is NULL and after is 356 & 400
  • If current id defined 190, the results as before is 210 & 102 and after is NULL

I try this SQL but not working fine,

$define_id = 400;

SELECT *
FROM table_name

WHERE ( 
    id = IFNULL(
        (
            SELECT MIN(id)
            FROM table_name
            WHERE id > $define_id
        ), 0 )

    OR id = IFNULL(
        (
            SELECT MAX(id)
            FROM table_name
            WHERE id < $define_id
        ), 0 )
) 

LIMIT 2

The code success to get before and after data, but only one before and one after. I want get result two before and two after.

Please help.

  • 写回答

3条回答 默认 最新

  • dtjwov4984 2017-11-26 13:56
    关注

    Tried with the outputs you mentioned in the question. If this is not you want please explain more clearly.

        CREATE TABLE IF NOT EXISTS `docs` (
      `primary_key` int(6) unsigned NOT NULL,
      `id` int(3) unsigned NOT NULL,
      PRIMARY KEY (`primary_key`,`id`)
    ) DEFAULT CHARSET=utf8;
    INSERT INTO `docs` (`primary_key`, `id`) VALUES
      ('1', '345'),
      ('2', '356'),
      ('3', '400'),
      ('4', '102'),
      ('5', '210'),
      ('6', '190');
    

    Query

    (SELECT * FROM docs WHERE primary_key < (SELECT primary_key FROM docs WHERE id = 400) ORDER BY primary_key DESC LIMIT 2)
    UNION ALL
    (SELECT * FROM docs WHERE primary_key > (SELECT primary_key FROM docs WHERE id = 400) ORDER BY primary_key ASC LIMIT 2);
    

    Sqlfiddle link http://sqlfiddle.com/#!9/e11c8d/2

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(2条)

报告相同问题?

悬赏问题

  • ¥100 关于使用MATLAB中copularnd函数的问题
  • ¥20 在虚拟机的pycharm上
  • ¥15 jupyterthemes 设置完毕后没有效果
  • ¥15 matlab图像高斯低通滤波
  • ¥15 针对曲面部件的制孔路径规划,大家有什么思路吗
  • ¥15 钢筋实图交点识别,机器视觉代码
  • ¥15 如何在Linux系统中,但是在window系统上idea里面可以正常运行?(相关搜索:jar包)
  • ¥50 400g qsfp 光模块iphy方案
  • ¥15 两块ADC0804用proteus仿真时,出现异常
  • ¥15 关于风控系统,如何去选择