Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1 2 След.
RSS
Как правильно построить SQL-запросы в VBA
 
#1
11.02.2014 17:48:52
Добрый день. Подскажите как решить такую проблему: есть excel-файл в котором находяться нужные (но избыточные) данные.
Из него с помощью метода ADODB вытаскиваю часть данных на другой лист этой же книги. Критерий по которому происходит отбор данных находится
на исходном листе в ячейке [i1] (это для примера имя Василь). А если мне нужно использовать несколько критериев для выбора из
большой базы данных (в файле-примере это имена "Іван, Володимир" в следующих ячейках), тоесть количество ФИО по которым
надо вытянуть информацию из базы около 50,  как в таком случае построить запрос? Или нужно будет генерировать 50 запросов?
 
 
 
#2
11.02.2014 18:31:28
можно и 50
возможно, это будет даже быстрее

а можно так
перед запросом:
Код
  s = "|" & VBA.Join(Application.Transpose(ActiveSheet.[i1].CurrentRegion), "|") & "|"
сам запрос:
Код
d = "Select * From [1$] Where '|' & f5 & '|' LIKE '" & s & "'"
(обратите внимание на одинарные кавычки - синтаксисом допускается)
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#3
11.02.2014 18:50:05
ikki, спасибо (особенно за одинарные кавычки в свое время с кавычками немало поломал голову).
что касаеться самого запроса то он после Вашых рекомендаций имеет вид:

Код
Select * From [1$] Where '|' & f5 & '|' LIKE '|Василь|Іван|Володимир|'
ну и по понятным причинам ничего не вытаскивает из "базы".
Я что то недопонял?
П.С. Это условно данные для критерия отбора находятся вертикально в 3 ячейках
в принципе я их и в массив могу (Array(n,n+1....)) важно понять как не делать 50 запросов
а обработать все одним (или это больше по части SQL)?
 
 
 
#4
11.02.2014 19:04:05
извините, ошибся - у меня старый Excel, а переписывать весь код для проверки не хотелось
запрос должен выглядеть так:
Код
d = "Select * From [1$] Where '" & s & "' LIKE '*|' & f5 & '|*'"

или так, с использованием функции VBA
Код
d = "Select * From [1$] Where InStr('" & s & "', '|' & f5 & '|')>0"
Изменено: ikki - 11.02.2014 19:13:48
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#5
11.02.2014 19:11:02
Спасибо, буду разбираться
П.С. А Excel по-видимому действительно старый  ;)  
 
 
 
#6
11.02.2014 19:12:22
2003-й.
мне хватает   :)

пс. ещё раз скопируйте код - опять наколбасил с кавычками :(
Изменено: ikki - 11.02.2014 19:14:51
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#7
11.02.2014 22:59:37
Знал что не работало еще когдпа писал предыдущее сообщение  :)
Просто неудобно было доставать
А завтра на работе обьязательно проверю последнюю версию кода
Еще раз спасибо
 
 
 
#8
11.02.2014 23:32:29
так и пришлось попробовать...
заодно вспомнить, что подстановочные знаки в SQL другие, чем в VBA
заодно убедиться, что у Вас в таблице дофига лишних пробелов в столбце с именами.
и что "Iван" в столбце I какой-то не такой (разбираться не стал, просто скопировал)

итого: оба способа рабочие
Изменено: ikki - 11.02.2014 23:33:06
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#9
11.02.2014 23:59:09
пс. само собой - без ADO (на массивах и словаре) макрос будет работать быстрее.
но интересно было всё-таки добить вариант с SQL
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#10
12.02.2014 02:46:39
Доброе время суток.
Если используется SQL, то всё же желательно разносить таблицы по листам. Так и сделал: таблица исходных данных на листе BaseSheet, список имён для выборки на листе QuerySheet, там же и кнопка запроса. Like лишняя конструкция - для этого лучше использовать Join.
Успехов.
 
 
 
#11
12.02.2014 10:08:26
ikki, таки да читал где-то на форуме что быстрее на словарях-массивах, но важно было именно с помощью ADODB
поскольку в "реале" вытаскивать данные собираюсь с базы SQL, а опыта построения таких запросов нет.
anvg, очень интересно написано (неисключаю что свой пример я строил на Вами ранее написаном макросе, поскольку "нарыл" его
где-то на просторах форума и чуть-чуть покорежил).
Уважаемые планетяне, может подскажите что почитать для построения SQL-запосов из VBA?
П.С. Сейчас штудирую Грабера "Понимание SQL". Как по мне там что-то с переводом (редакция 1992) ну и как мне кажется не весь функционал
применим для использования в VBA  
 
 
 
#12
12.02.2014 11:19:29
Цитата
(неисключаю что свой пример я строил на Вами ранее написаном макросе
Судя по некоторым особенностям именования - не исключено  ;)  . При запросах к таблицам Excel используется движок Access (с некоторыми особенностями обращения к таблицам на листе [ИмяЛиста$]), так что имеет смысл поискать книжки по его диалекту SQL. Следует также учитывать то, что типы данных проверяются по первым 8 строкам (по-умолчанию) таблицы, так что желательно в них иметь заполненные данные для правильной работы.
 
 
 
#13
12.02.2014 11:24:08
anvg, еще раз спасибо.
Ушел учится  :)
 
 
 
#14
12.02.2014 11:30:03
Можете глянуть мою надстройку, пока еще бета версия. Но данные вытянет откуда хотите.   :)  

http://files.mail.ru/20A7C9975D374B4FAB199D1148831349

Изменено: PowerBoy - 12.02.2014 11:31:52
Excel + SQL = Activetables
 
 
 
#15
12.02.2014 12:14:18
в офисе 97 была совершенно шикарная русская справка по ядру Microsoft Jet
и устанавливалась она по умолчанию.
сейчас глянул - в текущем 2003-м из Excel я её не вижу (видимо, настраивать надо), а из Access - прекрасно вижу.
пробежался по диагонали - вроде бы не хуже 97-го.

раздел так и называется - "Справочник Microsoft Jet SQL"
имхо, параллельное изучение с Грубером - неплохой вариант.
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#16
12.02.2014 12:21:08
зы. PowerBoy, а Вам не кажется, что Вы подобными постами (я их не первый раз вижу), нарушаете п.3.5 Правил форума?
причем из Ваших шести (на текущий момент) сообщений ровно половина - реклама Вашей надстройки.
со скринами на пол-страницы :(
Изменено: ikki - 12.02.2014 12:23:25
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#17
12.02.2014 12:25:52
Цитата
ikki пишет:
зы. PowerBoy , а Вам не кажется, что Вы подобными постами (я их не первый раз вижу), нарушаете п.3.5 Правил форума?
Не знаю -  разъясните что именно? Надстройка у меня бесплатная. К данной теме имеет прямое отношение.  
Excel + SQL = Activetables
 
 
 
#18
12.02.2014 12:38:04
Цитата
3.5. Рекламировать (использовать) в сообщениях, подписях, именах или аватарах любые товары и услуги, публиковать там ссылки на внешние интернет-ресурсы не имеющие отношения к обсуждаемой теме

"любые" в моём понимании - это любые.
в том числе бесплатные.
бесплатный товар всё равно является товаром.
а оговорка в Правилах про "не имеющие отношение" - относится к ссылкам (красная часть цитаты), а не к товарам и услугам (синяя часть).

и дискутировать тут не о чем.

впрочем, в Правилах есть ещё п.5.3.
им и карты в руки.
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#19
12.02.2014 13:46:35
ikki, ну это перебор. Надстройка имеет отношение к теме, я лично не вижу ничего плохого в этом. Притом тут даже не столько реклама, сколько файл. С таким же успехом можно тогда запретить и файлы с решениями выкладывать и полезными решениями.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#20
12.02.2014 13:51:40
ок. вопросов больше нет.
трактовать правила - очевидное право модераторов.
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#21
12.02.2014 17:00:43
а я думал тема исчерпана :)

ikki, конечно спасибо за еще один источник информации,
помню Вы говорили что у Вас старенький Excel но не думал что такие манускрипты (MS Office 97)  ;)

PowerBoy, к сожалению на работе (а я именно что на роботе) екзешник посмотреть не смогу
но хочу поблагодарить так как понимаю: стобы создать такого "монстра" нужно потратить неимоверную кучу усилий и времени

что же касается моей задачи то мне как раз и нужен "чистый" код поскольку я делаю какбы готовое решение не для себя (для сотрудников)
если бы это было не так я б всех не заморачивал, а использовал к примеру MS Query.
 
 
 
 
#22
12.02.2014 17:03:29
Цитата
Vitallic пишет:
MS Office 97
в 97-м году другого не было :D
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#23
20.02.2014 10:26:02
Всем привет
Научился заливать в двумерный массив через sql запрос один dbf файл.
Но вот не задача что делать если массив надо создать из нескольких dbf файлов находящихся в разных директориях ?
При этом использую Recordset, GetRows и Connection.
 
 
 
#24
21.02.2014 02:50:43
Доброе время суток
Tikr,
Цитата
если массив надо создать из нескольких dbf файлов находящихся в разных директориях
запрос для Recordset.Open будет выглядеть приблизительно так
Код
'...
Dim sSQL As String
Dim sConn As String
'...
sConn = "Provider=VFPOLEDB.1;Data Source='c:\temp\';Collate=russian;Codepage=866"
'...
sSQL = "Select t1.*,t2.* From ""c:\folder1\table1.dbf"" As t1 Inner Join ""c:\folder2\table2.dbf"" As t2 On (t1.Name=t2.Name)"

Естественно сам SQL запрос по требуемому смыслу. Успехов.
 
 
 
#25
21.02.2014 12:29:38
Спасибо за ответ
За это время я уже всю "нужную" базу залил в одну папку, подправил sql запрос. Если выгружать из одной таблицы, всё работает. И всё вроде бы хорошо но когда использую несколько таблиц. Возникает ошибка на строке
Код
Array = rs.GetRows
' Run time erroк 3021' BOF или EOF имеет значение True, либо текущая запись удалена. Для выполнения операции требуется текущая операция
По разному пробовал и все равно каждый раз выходит эта ошибка И незнаю в чем причина толи Recordset, толи Connection, а может вообще неправильный запрос (хоть на него программа и не ругается)
 
 
 
#26
21.03.2014 13:07:57
Добрый день, уважаемые коллеги!)
Касаемо последнего сообщения ошибка BOF или EOF возникла скорее всего из-за того что sql запрос ничего не посчитал и ничего не выдал Мож кому пригодится)
Помимо этого подскажите ещё. У меня есть таблица типа: Город, Продажи_фев, Продажи_мар выгрузка идет из одной базы, получается для одно колонки одно условие для другой другое
Правильно ли я понимаю таблицу надо создавать именно так и не иначе??
Select P1.Город, Р1.Сумма, Р2.Сумма
From (Select Город, Сумма, From Продажи, Where Месяц=фев) Р1,(Select Город, Сумма, From Продажи, Where Месяц=мар) Р2
WHERE P1.Город = P2.Город
...
А то у меня условие слишком много)))
 
 
 
#27
21.03.2014 14:05:16
Код
SELECT 
город,
SUM(iif(месяц="февраль",сумма,0)) AS сумма_фев,
SUM(iif(месяц="март",сумма,0)) AS сумма_март
FROM 
[Продажи$]
GROUP BY 
город
Excel + SQL = Activetables
 
 
 
#28
23.05.2014 08:26:45
Добрый день!
Подтолкните в правильном направлении
Есть таблица она создается за счёт все возможных связей из разных источников Основное условие в одной таблице,  включает x параметров (например n1, n2, n3 и т.д.), для разных агентов это условие разное (например n1+n2, n3, n2+n4) Получается для каждого такого условия создаю отдельный запрос, а потом всё сливаю union all и на выходе все продажи по все агентам идут в одном поле Вопрос можно ли как-то это оптимизировать?
 
 
 
#29
23.05.2014 08:49:29
Цитата
Tikr пишет:
Подтолкните в правильном направлении
Вы запрос свой покажите, то не очень понятно.
Excel + SQL = Activetables
 
 
 
#30
23.05.2014 09:15:20
Взял небольшой кусочек из основной портянки
Убрал все возможные связи, оставил только то в чем может отличаться запрос
Код
SELECT MONTH(BD.DATE) as DAT, MIN.AGENT, MIN.STROKA, Sum (([count]*([cena]/1000))) AS Summ1
FROM BD,  MIN  WHERE (MIN.PODGRUPA Is Null And MIN.NAIMEN Is Null And MIN.NOM_NAKL Is Null)
GROUP BY MONTH(BD.DATE), MIN.AGENT, MIN.STROKA
UNION all
SELECT MONTH(BD.DATE) as DAT, MIN.AGENT, MIN.STROKA, Sum (([count]*([cena]/1000))) AS Summ1
FROM BD, MIN WHERE (MIN.PODGRUPA Is Null And MIN.POLUCHA Is Null And MIN.NOM_NAKL Is Null)
GROUP BY MONTH(BD.DATE), MIN.AGENT, MIN.STROKA
 
 
 
Страницы: 1 2 След.
Читают тему
Наверх