从当前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.

我有一个包含数据库的表，我希望在当前id之前获取两个数据，并在当前id之后获取两个数据。

  primary_key id 
1 345 
2 356 
3 400 
4 102 
5 210 
6 190 
  code>  pre> 
 
   案例 strong>  p> 
 
 
如果当前ID定义为 400  strong>，则之前的结果为 356＆amp;  345  strong>和 102＆amp;  210  strong>  li> 
 
如果当前ID定义为 210  strong>，则之前的结果为 102＆amp;  400  strong>和
之后 190  strong>  li> 
 
如果当前ID定义为 356  strong>，则之前的结果为 345  强>和
之后 400＆amp;  102  strong>  li> 
 
如果当前ID定义为 345  strong>，则之前的结果为 NULL  strong>，而
 
后期 356＆amp;  400  strong>  li> 
 
如果当前ID定义为 190  strong>，则之前的结果为 210＆amp;  102  strong>和
后是 NULL  strong>  li> 
  ul> 
 
 我尝试这个SQL但不能正常工作， p> 
 \  n 
  $ define_id = 400; 
 
SELECT * 
FROM table_name 
 
WHERE（
 id = IFNULL（
（
 
 SELECT MIN（id）
 FROM table_name 
 WHERE id＆gt  ; $ define_id 
），0）
 
 OR id = IFNULL（
（
 
 SELECT MAX（id）
 FROM table_name 
 WHERE id＆lt; $ define_id 
），0）
）\  n 
LIMIT 2 
  code>  pre> 
 
 在数据之前和之后获取的代码成功，但只有一个在之前和之后。 我希望在之前和之后获得两个结果。 p> 
 
 
请帮助。 p> 
  div>

答

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

答

One method uses union all for the two groups:

(select 'before' as which, t.*
 from t cross join
      (select primary_key from t where id = $define_id) x
 where t.primary_key < x.primary_key
 order by t.primary_key desc
 limit 2
) union all
(select 'after' as which, t.*
 from t cross join
      (select primary_key from t where id = $define_id) x
 where t.primary_key > x.primary_key
 order by t.primary_key asc
 limit 2
);

答

You can do it with min and max functions, So for next

SELECT * FROM `table` WHERE  id = (select min(id) from `table` where id > YOUR_ID)

And for previous:

SELECT * from `table` where id = (select max(id) from table where id < YOUR_ID)

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

相关推荐