Я хотел бы выполнить запрос как
select ... as days where `date` is between '2010-01-20' and '2010-01-24'
И вернуть данные как:
дней ---------- 2010-01-20 2010-01-21 2010-01-22 2010-01-23 2010-01-24
Это решение использует нет циклов, процедур или временных таблиц. Подзапрос генерирует даты за последние 10 000 дней и может быть расширен, чтобы перейти так далеко назад или вперед, как вы хотите.
select a.Date
from (
select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a) ) DAY as Date
from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as d
) a
where a.Date between '2010-01-20' and '2010-01-24'
Выход:
Date
----------
2010-01-24
2010-01-23
2010-01-22
2010-01-21
2010-01-20
Примечания по производительности
Тестирование это Вот, производительность на удивление хорошая: вышеуказанный запрос занимает 0,0009 сек.
Если мы расширим подзапрос для генерации ок. 100 000 чисел (и, следовательно, около 274 лет дат), это работает в 0,0458 сек.
Кстати, это очень переносимый метод, который работает с большинством баз данных с небольшими изменениями.
Вот еще один вариант с использованием представлений:
CREATE VIEW digits AS
SELECT 0 AS digit UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9;
CREATE VIEW numbers AS
SELECT
ones.digit + tens.digit * 10 + hundreds.digit * 100 + thousands.digit * 1000 AS number
FROM
digits as ones,
digits as tens,
digits as hundreds,
digits as thousands;
CREATE VIEW dates AS
SELECT
SUBDATE(CURRENT_DATE(), number) AS date
FROM
numbers;
И тогда вы можете просто сделать (посмотреть, как это элегантно?):
SELECT
date
FROM
dates
WHERE
date BETWEEN '2010-01-20' AND '2010-01-24'
ORDER BY
date
Обновить
Стоит отметить, что вы сможете только генерировать прошедшие даты начиная с текущей даты. Если вы хотите сгенерировать любой диапазон дат (прошлые, будущие и промежуточные), вам придется использовать это представление:
CREATE VIEW dates AS
SELECT
SUBDATE(CURRENT_DATE(), number) AS date
FROM
numbers
UNION ALL
SELECT
ADDDATE(CURRENT_DATE(), number + 1) AS date
FROM
numbers;
Принятый ответ не работает для PostgreSQL (синтаксическая ошибка в или около «a»).
То, как вы делаете это в PostgreSQL, с помощью generate_series
функция, т.е.
SELECT day::date
FROM generate_series('2010-01-20', '2010-01-24', INTERVAL '1 day') day;
day
------------
2010-01-20
2010-01-21
2010-01-22
2010-01-23
2010-01-24
(5 rows)
Используя рекурсивное выражение общих таблиц (CTE), вы можете создать список дат, а затем выбрать его. Очевидно, что обычно вы не захотите создавать три миллиона дат, так что это просто иллюстрирует возможности. Вы можете просто ограничить диапазон дат внутри CTE и опустить предложение where в операторе select, используя CTE.
with [dates] as (
select convert(datetime, '1753-01-01') as [date] --start
union all
select dateadd(day, 1, [date])
from [dates]
where [date] < '9999-12-31' --end
)
select [date]
from [dates]
where [date] between '2013-01-01' and '2013-12-31'
option (maxrecursion 0)
На Microsoft SQL Server 2005 создание списка CTE всех возможных дат заняло 1:08. Генерация ста лет заняла меньше секунды.
MSSQL Query
select datetable.Date
from (
select DATEADD(day,-(a.a + (10 * b.a) + (100 * c.a)),getdate()) AS Date
from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4
union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4
union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4
union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
) datetable
where datetable.Date between '2014-01-20' and '2014-01-24'
order by datetable.Date DESC
Выход
Date
-----
2014-01-23 12:35:25.250
2014-01-22 12:35:25.250
2014-01-21 12:35:25.250
2014-01-20 12:35:25.250
Старое школьное решение сделать это без цикла / курсора — создать NUMBERS
таблица, которая имеет один столбец Integer со значениями, начинающимися с 1.
CREATE TABLE `example`.`numbers` (
`id` int(10) unsigned NOT NULL auto_increment,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Вам нужно заполнить таблицу достаточным количеством записей, чтобы удовлетворить ваши потребности:
INSERT INTO NUMBERS (id) VALUES (NULL);
Когда у вас есть NUMBERS
Таблицу можно использовать:
SELECT x.start_date + INTERVAL n.id-1 DAY
FROM NUMBERS n
JOIN (SELECT STR_TO_DATE('2010-01-20', '%Y-%m-%d') AS start_date
FROM DUAL) x
WHERE x.start_date + INTERVAL n.id-1 DAY <= '2010-01-24'
Абсолютное низкотехнологичное решение будет:
SELECT STR_TO_DATE('2010-01-20', '%Y-%m-%d')
FROM DUAL
UNION ALL
SELECT STR_TO_DATE('2010-01-21', '%Y-%m-%d')
FROM DUAL
UNION ALL
SELECT STR_TO_DATE('2010-01-22', '%Y-%m-%d')
FROM DUAL
UNION ALL
SELECT STR_TO_DATE('2010-01-23', '%Y-%m-%d')
FROM DUAL
UNION ALL
SELECT STR_TO_DATE('2010-01-24', '%Y-%m-%d')
FROM DUAL
Сформировать списки дат или номеров для того, чтобы присоединиться к. Вы должны сделать это, чтобы увидеть, где есть пропуски в данных, потому что вы ЛЕВЫЕ ПРИСОЕДИНЯЕТЕСЬ к списку последовательных данных — нулевые значения сделают очевидным, где существуют пропуски.
Для доступа 2010 — требуется несколько шагов; Я следовал той же схеме, что и выше, но думал, что смогу помочь кому-то в Access. Отлично сработало для меня, мне не нужно было хранить таблицу с датами.
Создайте таблицу с именем DUAL (аналогично тому, как работает таблица Oracle DUAL)
Создайте запрос с именем «ZeroThru9Q»; вручную введите следующий синтаксис:
SELECT 0 AS a
FROM dual
UNION ALL
SELECT 1
FROM dual
UNION ALL
SELECT 2
FROM dual
UNION ALL
SELECT 3
FROM dual
UNION ALL
SELECT 4
FROM dual
UNION ALL
SELECT 5
FROM dual
UNION ALL
SELECT 6
FROM dual
UNION ALL
SELECT 7
FROM dual
UNION ALL
SELECT 8
FROM dual
UNION ALL
SELECT 9
FROM dual;
Создайте запрос с именем «TodayMinus1KQ» (для дат до сегодняшнего дня); вручную введите следующий синтаксис:
SELECT date() - (a.a + (10 * b.a) + (100 * c.a)) AS MyDate
FROM
(SELECT *
FROM ZeroThru9Q) AS a,
(SELECT *
FROM ZeroThru9Q) AS b,
(SELECT *
FROM ZeroThru9Q) AS c
Создайте запрос с именем «TodayPlus1KQ» (для дат после сегодняшнего дня); вручную введите следующий синтаксис:
SELECT date() + (a.a + (10 * b.a) + (100 * c.a)) AS MyDate
FROM
(SELECT *
FROM ZeroThru9Q) AS a,
(SELECT *
FROM ZeroThru9Q) AS b,
(SELECT *
FROM ZeroThru9Q) AS c;
Создайте объединенный запрос с именем «TodayPlusMinus1KQ» (для дат +/- 1000 дней):
SELECT MyDate
FROM TodayMinus1KQ
UNION
SELECT MyDate
FROM TodayPlus1KQ;
Теперь вы можете использовать запрос:
SELECT MyDate
FROM TodayPlusMinus1KQ
WHERE MyDate BETWEEN #05/01/2014# and #05/30/2014#
Thx Pentium10 — вы заставили меня присоединиться к stackoverflow 🙂 —
это мое портирование на msaccess — думаю, оно будет работать на любой версии:
SELECT date_value
FROM (SELECT a.espr1+(10*b.espr1)+(100*c.espr1) AS integer_value,
dateadd("d",integer_value,dateserial([start_year], [start_month], [start_day])) as date_value
FROM (select * from
(
select top 1 "0" as espr1 from MSysObjects
union all
select top 1 "1" as espr2 from MSysObjects
union all
select top 1 "2" as espr3 from MSysObjects
union all
select top 1 "3" as espr4 from MSysObjects
union all
select top 1 "4" as espr5 from MSysObjects
union all
select top 1 "5" as espr6 from MSysObjects
union all
select top 1 "6" as espr7 from MSysObjects
union all
select top 1 "7" as espr8 from MSysObjects
union all
select top 1 "8" as espr9 from MSysObjects
union all
select top 1 "9" as espr9 from MSysObjects
) as a,
(
select top 1 "0" as espr1 from MSysObjects
union all
select top 1 "1" as espr2 from MSysObjects
union all
select top 1 "2" as espr3 from MSysObjects
union all
select top 1 "3" as espr4 from MSysObjects
union all
select top 1 "4" as espr5 from MSysObjects
union all
select top 1 "5" as espr6 from MSysObjects
union all
select top 1 "6" as espr7 from MSysObjects
union all
select top 1 "7" as espr8 from MSysObjects
union all
select top 1 "8" as espr9 from MSysObjects
union all
select top 1 "9" as espr9 from MSysObjects
) as b,
(
select top 1 "0" as espr1 from MSysObjects
union all
select top 1 "1" as espr2 from MSysObjects
union all
select top 1 "2" as espr3 from MSysObjects
union all
select top 1 "3" as espr4 from MSysObjects
union all
select top 1 "4" as espr5 from MSysObjects
union all
select top 1 "5" as espr6 from MSysObjects
union all
select top 1 "6" as espr7 from MSysObjects
union all
select top 1 "7" as espr8 from MSysObjects
union all
select top 1 "8" as espr9 from MSysObjects
union all
select top 1 "9" as espr9 from MSysObjects
) as c
) as d)
WHERE date_value
between dateserial([start_year], [start_month], [start_day])
and dateserial([end_year], [end_month], [end_day]);
ссылающиеся на MSysObjects просто «потому что для доступа нужен счетчик таблиц» как минимум в 1 записи, в предложении from — подойдет любая таблица с хотя бы 1 записью.