Что такое 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
