Что такое SQL для "next" и "previous" в таблице?

У меня есть таблица элементов, каждая из которых имеет дату, связанную с ней. Если у меня есть дата, связанная с одним элементом, как я могу запросить базу данных с SQL для получения "предыдущих" и "последующих" элементов в таблице?

Невозможно просто добавить (или вычесть) значение, так как даты не имеют регулярного промежутка между ними.

Одним из возможных приложений может быть ссылка "предыдущий/следующий" в фотоальбоме или веб-приложении блога, где базовые данные находятся в таблице SQL.

Я думаю, что возможны два случая:

Во-первых, где каждая дата уникальна:

Пример данных:

1,3,8,19,67,45

Какой запрос (или запросы) дал бы 3 и 19, если в качестве параметра был указан параметр 8? (или строк 3,8,19). Обратите внимание, что не всегда возвращаются три строки - на концах последовательности будет отсутствовать.

Во-вторых,, если имеется отдельный уникальный ключ для упорядочения элементов, каков запрос, чтобы вернуть набор "окружающая" дата? Ожидается, что порядок будет по дате, а затем по ключу.

Пример данных:

(key:date) 1:1,2:3,3:8,4:8,5:19,10:19,11:67,15:45,16:8

Какой запрос для '8' возвращает набор:

2:3,3:8,4:8,16:8,5:19

или какой запрос генерирует таблицу:

key date prev-key next-key
1   1    null     2
2   3    1        3
3   8    2        4
4   8    3        16
5   19   16       10
10  19   5        11
11  67   10       15
15  45   11       null
16  8    4        5

Порядок таблиц не важен - только поля next-key и prev-key.

Оба TheSoftwareJedi и Cade Roux имеют решения, которые работают для наборов данных, которые я опубликовал вчера вечером. По второму вопросу оба кажется неудачным для этого набора данных:

(key:date) 1:1,2:3,3:8,4:8,5:19,10:19,11:67,15:45,16:8

Ожидаемый заказ по дате, тогда ключ, поэтому один ожидаемый результат может быть:

2:3,3:8,4:8,16:8,5:19

а другой:

key date prev-key next-key
1   1    null     2
2   3    1        3
3   8    2        4
4   8    3        16
5   19   16       10
10  19   5        11
11  67   10       15
15  45   11       null
16  8    4        5

Порядок таблиц не важен - только поля next-key и prev-key.

Ответ 1

Моя собственная попытка набора решения, основанного на TheSoftwareJedi.

Первый вопрос:

select date from test where date = 8
union all
select max(date) from test where date < 8
union all
select min(date) from test where date > 8
order by date;

Второй вопрос:

При отладке я использовал набор данных:

(key:date) 1:1,2:3,3:8,4:8,5:19,10:19,11:67,15:45,16:8,17:3,18:1

чтобы дать этот результат:

select * from test2 where date = 8
union all
select * from (select * from test2
                   where date = (select max(date) from test2 
                                     where date < 8)) 
    where key = (select max(key) from test2 
                    where date = (select max(date) from test2 
                                      where date < 8))
union all
select * from (select * from test2
                   where date = (select min(date) from test2 
                                     where date > 8)) 
    where key = (select min(key) from test2 
                    where date = (select min(date) from test2 
                                      where date > 8))
order by date,key;

В обоих случаях предложение final order by строго говоря необязательно.

Ответ 2

Выберите max (элемент) из данных, где элемент < 8

Union

Выберите min (element) из Data Where Element > 8

Но, как правило, более полезно думать о sql для заданных ориентированных операций, а не для итеративной операции.

Ответ 3

Самосоединения.

Для таблицы:

/*
CREATE TABLE [dbo].[stackoverflow_203302](
    [val] [int] NOT NULL
) ON [PRIMARY]
*/

С параметром @val

SELECT cur.val, MAX(prv.val) AS prv_val, MIN(nxt.val) AS nxt_val
FROM stackoverflow_203302 AS cur
LEFT JOIN stackoverflow_203302 AS prv
    ON cur.val > prv.val
LEFT JOIN stackoverflow_203302 AS nxt
    ON cur.val < nxt.val
WHERE cur.val = @val
GROUP BY cur.val

Вы можете сделать это хранимой процедурой с выходными параметрами или просто соединить это как коррелированный подзапрос с данными, которые вы тянете.

Без параметра для ваших данных результат будет следующим:

val         prv_val     nxt_val
----------- ----------- -----------
1           NULL        3
3           1           8
8           3           19
19          8           45
45          19          67
67          45          NULL

Для модифицированного примера вы используете это как коррелированный подзапрос:

/*
CREATE TABLE [dbo].[stackoverflow_203302](
    [ky] [int] NOT NULL,
    [val] [int] NOT NULL,
    CONSTRAINT [PK_stackoverflow_203302] PRIMARY KEY CLUSTERED (
        [ky] ASC
    )
)
*/

SELECT cur.ky AS cur_ky
        ,cur.val AS cur_val
        ,prv.ky AS prv_ky
        ,prv.val AS prv_val
        ,nxt.ky AS nxt_ky
        ,nxt.val as nxt_val
FROM (
    SELECT cur.ky, MAX(prv.ky) AS prv_ky, MIN(nxt.ky) AS nxt_ky
    FROM stackoverflow_203302 AS cur
    LEFT JOIN stackoverflow_203302 AS prv
        ON cur.ky > prv.ky
    LEFT JOIN stackoverflow_203302 AS nxt
        ON cur.ky < nxt.ky
    GROUP BY cur.ky
) AS ordering
INNER JOIN stackoverflow_203302 as cur
    ON cur.ky = ordering.ky
LEFT JOIN stackoverflow_203302 as prv
    ON prv.ky = ordering.prv_ky
LEFT JOIN stackoverflow_203302 as nxt
    ON nxt.ky = ordering.nxt_ky

С выходом, как ожидалось:

cur_ky      cur_val     prv_ky      prv_val     nxt_ky      nxt_val
----------- ----------- ----------- ----------- ----------- -----------
1           1           NULL        NULL        2           3
2           3           1           1           3           8
3           8           2           3           4           19
4           19          3           8           5           67
5           67          4           19          6           45
6           45          5           67          NULL        NULL

В SQL Server я предпочитаю сделать подзапрос общим выражением таблицы. Это приводит к тому, что код выглядит более линейным, менее вложенным и более простым в использовании, если в нем много гнезд (также требуется меньшее повторение для некоторых повторных подключений).

Ответ 4

Во-первых, это должно работать (важно значение ORDER BY):

select min(a)
from theTable
where a > 8

select max(a)
from theTable
where a < 8

На второй вопрос, который я просил вас спросить...:

    select * 
    from theTable
    where date = 8

    union all

    select *
    from theTable
    where key = (select min(key) 
                 from theTable
                 where key > (select max(key)
                              from theTable
                              where date = 8)
                )

    union all

    select *
    from theTable
    where key = (select max(key) 
                 from theTable
                 where key < (select min(key)
                              from theTable
                              where date = 8)
                )

    order by key

Ответ 5

SELECT 'next' AS direction, MIN(date_field) AS date_key
FROM table_name
  WHERE date_field > current_date
GROUP BY 1 -- necessity for group by varies from DBMS to DBMS in this context
UNION
SELECT 'prev' AS direction, MAX(date_field) AS date_key
  FROM table_name
  WHERE date_field < current_date
GROUP BY 1
ORDER BY 1 DESC;

Выдает:

direction  date_key
---------  --------
prev              3
next             19

Ответ 6

Если ваша RDBMS поддерживает LAG и LEAD, это просто (Oracle, PostgreSQL, SQL Server 2012)

Они позволяют выбрать строку с каждой стороны любой заданной строки в одном запросе

Ответ 7

Попробуйте это...

SELECT TOP 3 * FROM YourTable
WHERE Col >= (SELECT MAX(Col) FROM YourTable b WHERE Col < @Parameter)
ORDER BY Col