用于在MySql中生成上一个和下一个链接的Sql [英] Sql for generating previous and next links in MySql

问题描述

这让我完全难住了.从一组数字 - 对于这个例子 1 到 20，我试图做上一个和下一个链接，所以它们最终是这样的:我将提供开始的数字.

This has me completely stumped. From a set of numbers - for this example 1 to 20, I'm trying to do previous and next links, so they end up like this: I'll be supplying the number to start from.

1       6           11          16
2       7           12          17
3       8           13          18
4       9           14          19
5       10          15          20
n-5    p-6 n-10   p-11 n-15    p-16

你知道如何为这样的事情做 sql 吗?

Have you any idea how to do the sql for something like this?

我试图以此为基础，但没有运气:

I was trying to build on this one, but no luck:

SELECT c.id,
  (SELECT MAX(p.id) FROM mytable p WHERE p.id < c.id AND p.country = 'us') prev_id,
  (SELECT MIN(n.id) FROM mytable n WHERE n.id > c.id AND n.country = 'us') next_id
FROM mytable as c WHERE c.id = 5;

表格数据

    "id"    "country"
"1"     "US"
"2"     "US"
"3"     "US"
"4"     "US"
"5"     "US"
"6"     "US"
"7"     "US"
"8"     "US"
"9"     "US"
"10"    "US"
"11"    "US"
"12"    "US"

期望输出

我提供的数字 = 10

number provided by me = 10

prev  | next
-------------
6     | 10

推荐答案

这里是MySQL解决方案:

SET @number=15;
SET @counter=0;


SELECT x.id, @number as next
FROM (
SELECT id, (@counter:=@counter+1) as line FROM TableData WHERE id <=@number ORDER BY id 
) x
WHERE x.line = @counter-5;

number 是您指定的.

您可以在 SQL Fiddle 中了解它的工作原理.随意更改其数据并进行测试，

You can see how it works in SQL Fiddle. Feel free to change its data and test,

这篇关于用于在MySql中生成上一个和下一个链接的Sql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！