Это всё хорошо, но в Access'ом SQL'е данная комбинация не работает.24.02.11 11:32 Число просмотров: 1527 Автор: Vedrus <Serokhvostov Anton> Статус: Member
[SQL] Можно ли в SQL-запросе общащаться к перменной типа Date как к структуре?24.02.11 08:19 Автор: Vedrus <Serokhvostov Anton> Статус: Member Отредактировано 24.02.11 08:20 Количество правок: 1
Работаю в MS Ассеss. Есть таблица list (id, date, title). Поле date имеет тип «Полный формат даты» (например, 19.06.1994 17:34:23). Можно ли с помощью SQL-запроса выбрать записи с конкретным днём, месяцем или годом? Т.е. можно ли как-то исхитриться, чтобы заработал такой запрос: «SELECT * FROM list WHERE date.year = 1994»?
У меня есть идеи по поводу того, как решить эту задачи, но они громоздкие, хотелось бы найти что-нибудь попроще. Одна из идей – это перед выполнением запроса динамически генерировать временную таблицу, где под каждый параметр даты отводить отдельное поле и делать выборку уже из этой таблицы.
Как вариант, дату можно представить iso форматом (yyyymmdd...24.02.11 14:43 Автор: Den <Денис Т.> Статус: The Elderman Отредактировано 24.02.11 14:44 Количество правок: 1
Как вариант, дату можно представить ISO форматом (YYYYMMDD или YYYYMMDDhhmmss) в строке и работать как со строкой. Например:
select * from list where date like '1994%' -- YEAR
select * from list where date like '____06%' -- MONTH
select * from list where date like '______19%' -- DAY
Большое спасибо, с LIKE’ом действительно, получается то, что...25.02.11 11:15 Автор: Vedrus <Serokhvostov Anton> Статус: Member
Большое спасибо, с LIKE’ом действительно, получается то, что нужно (только в аксесе вместа знака % используется *, а вместо _ - ?). Вот только появилась новая проблема – каким образом формировать динамически нужный запрос?
Т.е. у меня есть две таблицы: list (id, date, title), years (id, title), Есть запрос «SELECT * FROM list WHERE date like '???12*';». Если я просто создам запрос обычными средствами Access и помещу туда данную строку, то всегда будет выполняться одно и то же.
Средствами Access можно в подчинённой форме указывать запрос, вот только я не понимаю, как передавать ему параметры. Допустим. есть форма-таблица со списком годов. Как сделать так, чтобы при нажатии на «+» управление передавалось в процедуру VBA, которая бы подставляла в LIKE соответствующую строку? Пробовал на разные события вешать обработчик, но что-то не получилось.
Если я сделаю такой запрос: «SELECT * FROM list WHERE date like mask», то как мне вовремя подставить нужное значение в переменную mask, чтобы не выпадало окошко для ввода параметра вручную?
непосретственно из запроса ты можешь ссылаться на переменную...25.02.11 20:38 Автор: Den <Денис Т.> Статус: The Elderman Отредактировано 25.02.11 20:41 Количество правок: 2
непосретственно из запроса ты можешь ссылаться на переменную или объект формы, типа:
select * from list where list.date like Str(Year([frmMy]![editDate])) & "*"
или
select * from list where list.date like Str(DatePart("yyyy"), [frmMy]![editDate]) & "*"
еще можно строить запрос собираю строку в коде VB и записывать в свойство .SQL запроса:
myquery.sql = "select * from list where list.date like '" & Str(Year(Me.editDate.value)) & "*' "
Что-то не получается. пищу: «select * from list where date...01.03.11 10:58 Автор: Vedrus <Serokhvostov Anton> Статус: Member
Что-то не получается. Пищу: «SELECT * FROM list WHERE date Like '??????' & Str(year!title);». Форма открывается, запрос выполняется, editbox из формы year!title видит (окошко с запросом ввода переменной не выскакивает), но запрос возвращается пустым. Если вручную вместо «Str(year!title)» подставить, например, ‘2000’, то запрос выполняется адекватно. Что я делаю не так.
Ещё по второму случаю вопрос. Не совсем понятна последовательность действий. Вот у меня есть родительская форма… А дальше не понятно, что делать. Когда запрос уже есть всё ясно – создаём подчинённую форму и связываем её с этим запросом, а в случае, если я динамически запрос создаю, то что делать после создания родительской формы? Если подчинённую форму создавать, то что ей в источник записей ставить, а если этот динамически сформированный запрос записывать в реально существующий, то по какому событию это делать? Я пытался методом тыка события перебрать, чтобы до выполнения запроса вклиниться, но что то так и не нашёл нужного.
Ещё меня интересует, как непосредственно из VB с запросами...02.03.11 14:11 Автор: Vedrus <Serokhvostov Anton> Статус: Member
Ещё меня интересует, как непосредственно из VB с запросами работать. Полный вопрос в верхнем сообщении. В бейсике хоть отладку можно вести, можно увидеть, что там в запросе получилось...
Например так:02.03.11 23:36 Автор: Den <Денис Т.> Статус: The Elderman Отредактировано 02.03.11 23:39 Количество правок: 4
Dim qryMy as QeryDef
Set qryMy = CurrentDatabase.QueryDefs("MyQuery")
With qryMy
.SQL = "SELECT *" & vbNewLine & _
" FROM List" & vbNewLine & _
" WHERE timestamp >= #01.01." & CStr(Year(Now())) & "#" & vbNewLine & _
" AND timestamp < #01.01." & CStr(Year(Now())+1) & "#"
End With
---
P.S. вместо CurrentDatabase нужно использовать то, что рекомендуется для конкретной версии Access и конкретного типа проекта. F1 тебе в помощь!
"select * from list where date like '??????" &... [upd]01.03.11 12:02 Автор: Den <Денис Т.> Статус: The Elderman Отредактировано 01.03.11 12:30 Количество правок: 2
Моя ошибка. в VB надо использовать функцию CStr, а не Str [upd]01.03.11 13:44 Автор: Den <Денис Т.> Статус: The Elderman Отредактировано 01.03.11 23:33 Количество правок: 3
Попробовал последовать вашей рекомендации, и совершенно запутался. Не представляю, как запрос отлаживать, чтобы посмотреть, что там за строка для LIKE'а получилась. Вы говорите про ISO-формат. Я поменял тип поля tile вместо строки на краткий формат даты, например, 01.01.1900. Но если это уже не строка, то в каком виде оно доходит до запроса? Со строкой хоть можно было предсказуемый результат получить, а тут вообще непонятно, что в LIKE для примера вписывать. Например, 01.01.1900 в каком виде доходит? 01011900, 19000101 или что-то ещё?
PS. С CStr тоже не работает.
PPS. А если я не строкой, а числом буду иднекс по годам делать?Это же тоже должно быстро работать? Зачем мне дни и месяцы в иднексе, где только год нужен.
оператор LIKE проверяет строковое выражение (символьное поле...02.03.11 23:16 Автор: Den <Денис Т.> Статус: The Elderman Отредактировано 03.03.11 14:41 Количество правок: 2
> Попробовал последовать вашей рекомендации, и совершенно > запутался. Не представляю, как запрос отлаживать, чтобы > посмотреть, что там за строка для LIKE'а получилась. Вы > говорите про ISO-формат.
оператор LIKE проверяет строковое выражение (символьное поле таблицы) на совпадение заданному шаблону.
> Я поменял тип поля tile вместо > строки на краткий формат даты, например, 01.01.1900. Но > если это уже не строка, то в каком виде оно доходит до > запроса?
Ни в каком. Некоторые БД работают с датой как со строкой, но в случае Access, это врядли получится.
> Со строкой хоть можно было предсказуемый результат > получить, а тут вообще непонятно, что в LIKE для примера > вписывать. Например, 01.01.1900 в каком виде доходит? > 01011900, 19000101 или что-то ещё?
если решил использовать формат даты, то можно воспользоваться советом kstati и использовать оператор between. Но если предполагается использовать только выборку за год, то проще использовать >= и <
Например
WHERE list.timestamp >= #01.01.2010# AND list.timestamp < #01.01.2011#
проще некуда и приобразовывать ничего не надо.
> PS. С CStr тоже не работает.
Функция СStr() всего лишь приобразовывает данные числового формата в строку.
> PPS. А если я не строкой, а числом буду иднекс по годам > делать?Это же тоже должно быстро работать? Зачем мне дни и > месяцы в иднексе, где только год нужен.
а смысл? я до сих пор не могу понять цели всех этих манипуляций. ты скажи - что в итоге надо получить?
в таком случае используй between. В запросах с like и функциями типа Year по дате приходится смотреть все кортеджи, ибо, как ты и сказал "это уже не строка" - приходится производить преобразование даты в строку, затем сравнивать like,02.03.11 14:19 Автор: kstati <Евгений Борисов> Статус: Elderman Отредактировано 02.03.11 14:21 Количество правок: 3
> Работаю в MS Ассеss. Есть таблица list (id, date, title). > Поле date имеет тип «Полный формат даты» (например, > 19.06.1994 17:34:23). Можно ли с помощью SQL-запроса > выбрать записи с конкретным днём, месяцем или годом? Т.е. > можно ли как-то исхитриться, чтобы заработал такой запрос: > «SELECT * FROM list WHERE date.year = > 1994»?
Не знаю, есть ли это в убогом Аксессе, но в MySQL есть оператор BETWEEN. С его помощью ты сможешь выбрать записи от 01.01.1994 00:00:00 до 31.12.1994 23:59:59
Вообще-то в MySQL это делается по другому ) и гораздо изящнее:24.02.11 11:05 Автор: Fighter <Vladimir> Статус: Elderman Отредактировано 24.02.11 11:06 Количество правок: 1
> > Работаю в MS Ассеss. Есть таблица list (id, date, > title). > > Поле date имеет тип «Полный формат даты» (например, > > 19.06.1994 17:34:23). Можно ли с помощью SQL-запроса > > выбрать записи с конкретным днём, месяцем или годом? > Т.е. > > можно ли как-то исхитриться, чтобы заработал такой > запрос: > > «SELECT * FROM list WHERE date.year > = > > 1994»? > > Не знаю, есть ли это в убогом Аксессе, но в MySQL есть > оператор BETWEEN. С его помощью ты сможешь выбрать записи > от 01.01.1994 00:00:00 до 31.12.1994 23:59:59 Вообще-то в MySQL эт оделается по другому )
SELECT * FROM list WHERE YEAR(`date`)='1994'
где YEAR() - функция, возвращающая номер года той даты, которую ей скормили. Есть еще функции DAY() и MONTH(), если нужно.
Неоптимальное с точки зрения индекса решение, если я не...24.02.11 12:15 Автор: Winer <Виктор С.> Статус: Member
> > Не знаю, есть ли это в убогом Аксессе, но в MySQL есть > > оператор BETWEEN. С его помощью ты сможешь выбрать > записи > > от 01.01.1994 00:00:00 до 31.12.1994 23:59:59 > Вообще-то в MySQL эт оделается по другому ) > SELECT * FROM list WHERE YEAR(`date`)='1994' > где YEAR() - функция, возвращающая номер года той даты, > которую ей скормили. Есть еще функции DAY() и MONTH(), если > нужно.
Неоптимальное с точки зрения индекса решение, если я не ошибаюсь. Надо перелопатить все строки с применением к ним функции и сравнить с необходимым значением. В случае BETWEEN, если я не ошибаюсь, индексы нормально задействуются.
Отнюдь ) при использовании between индексы используюся активно. Кстати говоря напоминаю, что есть очень удобный инструмент анализа запросов. имя ему `describe` ;)[upd]01.03.11 23:06 Автор: kstati <Евгений Борисов> Статус: Elderman Отредактировано 02.03.11 01:15 Количество правок: 14
Одна из задач индексов - быстрая сортировка значений для выборки.
В случае использования between по индексированному полю идёт поиск малого значения, затем выдача результата вплоть до большего значения.
Выгода очевидна, по скорости. А причина - количество затрагиваемых строк (в моём случае 30480 против 762707), которые обусловлены типом запроса range и index соответственно; При этом вариант "where YEAR(`d`) " даже не пытается использовать ключи.
Теста ради набил таблицу хламом - случайными датами за 50 лет:
insert into tests.date_test values (NOW() - interval floor(rand()*365*50) day);
А за тем выбрал данные за 2001 год.
Если работа ведётся именно с диапазоном, то between рулит. Ясное дело, если нужно выбрать к примеру все первые числа, то ой )
mysql> describe date_test;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| d | date | YES | MUL | NULL | |
+-------+------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> select distinct count(*) from date_test;
+----------+
| count(*) |
+----------+
| 762061 |
+----------+
1 row in set (0.77 sec)
mysql> select count(*) from date_test where YEAR(`d`) = 2001;
+----------+
| count(*) |
+----------+
| 15401 |
+----------+
1 row in set (0.80 sec)
mysql> select count(*) from date_test where `d` like "2001%";
+----------+
| count(*) |
+----------+
| 15401 |
+----------+
1 row in set, 1 warning (0.84 sec)
mysql> select count(*) from date_test where `d` between '2001-01-01' and '2001-12-31';
+----------+
| count(*) |
+----------+
| 15401 |
+----------+
1 row in set (0.02 sec)
mysql> describe select count(*) from date_test where `d` between '2001-01-01' and '2001-12-31';
+----+-------------+-----------+-------+---------------+------+---------+------+-------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------+---------------+------+---------+------+-------+--------------------------+
| 1 | SIMPLE | date_test | range | i | i | 4 | NULL | 30480 | Using where; Using index |
+----+-------------+-----------+-------+---------------+------+---------+------+-------+--------------------------+
1 row in set (0.00 sec)
mysql> describe select count(*) from date_test where YEAR(`d`) = 2001;
+----+-------------+-----------+-------+---------------+------+---------+------+--------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------+---------------+------+---------+------+--------+--------------------------+
| 1 | SIMPLE | date_test | index | NULL | i | 4 | NULL | 762707 | Using where; Using index |
+----+-------------+-----------+-------+---------------+------+---------+------+--------+--------------------------+
mysql> describe select count(*) from date_test where `d` like "2001%";
+----+-------------+-----------+-------+---------------+------+---------+------+--------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------+---------------+------+---------+------+--------+--------------------------+
| 1 | SIMPLE | date_test | index | i | i | 4 | NULL | 762707 | Using where; Using index |
+----+-------------+-----------+-------+---------------+------+---------+------+--------+--------------------------+
1 row in set, 1 warning (0.00 sec)
---
[upd] Как и ожидал between даёт эффект и при работе с or.
То есть поиск данных за определённый месяц тоже работает на ура, хотя выгода по времени уже не на столько заметна.
<?
$sql = "select count(*) from date_test where ";
$tmp = array();
for ($i=1961; $i<=2011; $i++) {
$tmp[] = "`d` between '$i-01-01' and '$i-01-31'";
}
$sql .= join(" or ", $tmp);
?>
---
Преобразуется в длиннющий запрос:
select count(*) from date_test where `d` between '1961-01-01' and '1961-01-31' or ... or `d` between '2011-01-01' and '2011-01-31
select count(*) from date_test where `d` between '1961-01-01'....<откусил>
+----------+
| count(*) |
+----------+
| 64615 |
+----------+
1 row in set (0.45 sec)
mysql> describe select count(*) from date_test where `d`...<откусил>
+----+-------------+-----------+-------+---------------+------+---------+------+-------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------+---------------+------+---------+------+-------+--------------------------+
| 1 | SIMPLE | date_test | range | i | i | 4 | NULL | 64566 | Using where; Using index |
+----+-------------+-----------+-------+---------------+------+---------+------+-------+--------------------------+
1 row in set (0.00 sec)
mysql> select count(*) from date_test where d like __01%';
+----------+
| count(*) |
+----------+
| 64615 |
+----------+
1 row in set, 1 warning (0.92 sec)
mysql> describe select count(*) from date_test where d like __01%';
+----+-------------+-----------+-------+---------------+------+---------+------+--------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------+---------------+------+---------+------+--------+--------------------------+
| 1 | SIMPLE | date_test | index | NULL | i | 4 | NULL | 762561 | Using where; Using index |
+----+-------------+-----------+-------+---------------+------+---------+------+--------+--------------------------+
1 row in set, 1 warning (0.00 sec)
---
В общем, не стоит отмахиваться от between ) реально удобно и быстро.
И, кстати, если внимательно приглядеться к выборке длиннющим запросом, то количество затрагиваемых тестами строк меньше чем количество результирующих строк. Сильно копошиться ломает, но предполагаю, что дело в бинарном поиске.
Ага, таки сильно ошибался я, а не Winer02.03.11 00:31 Автор: Fighter <Vladimir> Статус: Elderman
подробно о проекте
Анализ криптографических сетевых...
новое сообщение
закрытая нитка
новое сообщение
в закрытой нитке
старое сообщение
