Next and previus rows in a mysql query
How can I select next and previous rows for a total of six rows in a query but with conditions:
I'll always have 6 rows, so if i have:
1, 2, 3, 4, 5, 6, 7, 8, 9
and my ID is 2 the result must be:
1,3,4,5,6,7
if my ID is 5 the result must be:
2,3,4,6,7,8
if my ID is 9 the result must be:
3,4,5,6,7,8
edit:
(SELECT * FROM articulos
WHERE categoria = 'Y'
AND numero >=
(SELECT IF(
(SELECT COUNT(*) FROM articulos WHERE numero < X AND categoria = 'Y')<3,
(SELECT MIN(X) FROM articulos WHERE categoria = 'Y'),
(X-3)
))
ORD开发者_运维问答ER by numero ASC LIMIT 6)
UNION
(SELECT * FROM articulos
WHERE categoria = 'Y'
AND numero <=
(SELECT IF(
(SELECT COUNT(*) FROM articulos WHERE numero > 1605 AND categoria = 'Y')<3,
(SELECT MAX(X) FROM articulos WHERE categoria = 'Y'),
(X+3)
))
ORDER by numero DESC LIMIT 6)
maybe this is the solution
Something like this:
SELECT data
FROM table
JOIN ( SELECT
CASE
WHEN n > MAX(id)-3 THEN MAX(id) - 7
WHEN n < 4 THEN 0
ELSE n - 4
END AS lower_bound,
CASE
WHEN n > MAX(id)-3 THEN MAX(id) + 1
WHEN n < 4 THEN 7
ELSE n + 4
END AS upper_bound
FROM table ) AS t1
WHERE id BETWEEN t1.lower_bound AND t1.upper_bound
AND id <> n
should always select 6 rows
More info on the CASE
function in the docs
精彩评论