Как сделать sql запрос к нескольким таблицам (ADO,MS Access)?

← →
gedevan (2016-01-08 14:52) [0]

есть несколько таблиц которые на выходе запроса я хочу превратить в одну. Причем в интернете описывается как одну таблицу присоединить к другой (т.е. делается соединение приклеиванием справа колонок другой таблицы). Мне же нужно, чтобы мои таблицы в результирующей выборки шли одна за другой. Причем в таблицах есть как одинаковые колонки, так и уникальные для каждой таблицы. Т.е. как сделать чтобы в итоговой выборке совпадающие по имени колонки совместились (по вертикали), а несовпадающие также присутствовали в итоговой выборке, но в тех частях данных которые соответствуют таблицам где такой колонки нет, там просто были бы пустые значения. И еще как прикрутить к этой таблице колонку "Имя таблицы" и записать в него имя таблицы из которой собственно эти данные в выборку и попали?

Сейчас я сделал отдельный запрос к каждой таблице, работает жутко медленно, вот и возник вопрос, надеюсь что одним запросом быстрее получится.

← →
gedevan (2016-01-08 14:59) [1]

т.е. еще раз команда JOIN, это не то что мне нужно. В таблицах нет данных которые в результирующей выборке должны оказаться в одной строке. В итоговой выборке количество строк должно быть равно сумме количеств строк в объединяемых таблицах.

← →
gedevan (2016-01-08 15:22) [2]

в общем понял что нужно юзать UNION не понятно только что делать с различающимися полями, как их добавить с пустым значением в запросы к таблицам в которых таких полей нет.

← →
gedevan (2016-01-08 21:30) [3]

SQLText := "Select ""Tem"",ShotNumber," + "Name,ItemName,NumberOfSearch,tempfound,itIsTemplate,anotherTime" + "SearchFixed,x1,x2,y1,y2,ksov,searchtime,FourSearch,NameConnectedScreen,NULL " + "FROM Templates where ShotNumber = :prm" + "UNION" + "Select ""Dyn"",ShotNumber, " + "Name,NumberOfSegment,null,null,null,null,null,x1,x2,y1,y2,null,searchtime," + "null,NameConnectedScreen,NULL " + "FROM DynObj where ShotNumber = :prm" + "UNION" + "Select ""Col"",ShotNumber, " + "Name,NumberOfSegment,null,null,null,null,null,x1,x2,y1,y2,null,searchtime," + "null,NameConnectedScreen,NULL " + "FROM ColorSearch where ShotNumber = :prm" + "UNION" + "Select ""Tex"",ShotNumber, " + "Name,NumberOfSegment,null,null,null,null,null,x1,x2,y1,y2,null,searchtime," + "null,NameConnectedScreen,RecognizedText " + "FROM TextRecognize where ShotNumber = :prm"; ADOQuery := TADOQuery.Create(DebugFilmConnection); ADOQuery.Connection := DebugFilmConnection; ADOQuery.Active := false; //ADOQuery.SQL.Text := "SELECT * FROM Templates where ShotNumber = :prm"; ADOQuery.SQL.Text := SQLText; ADOQuery.Parameters.ParseSQL(ADOQuery.SQL.Text, true); ADOQuery.Parameters.ParamByName("prm").Value := number; ADOQuery.Active := true; ADOQuery.ExecSQL;

Вот что у меня родилось, но он ругается на Неправильно определен объект Parameter. Предоставлены несогласованные или неполные сведения.

А если вместо ADOQuery.SQL.Text := SQLText; оставить закоментированную строку, то все работает...

что не так :-(

← →
gedevan (2016-01-08 21:37) [4]

и еще я не понимать, как называется первая и последняя колонка в этом запросе (т.к. в реальной таблице первой колонки нет вообще, а последняя есть только в последней таблице)? да и вообще как он названия определяет колонок?

← →
Германн © (2016-01-09 00:04) [5]

Ты бы хоть привел структуры этих таблиц, их взаимосвязи (если они есть) и что ты хочешь получить в наборе данных в результате своего запроса. На форумах нет экстрасенсов.

← →
sniknik © (2016-01-09 00:37) [6]

> Неправильно определен объект Parameter.
задано 4-ре, определен 1, естественно остальные неправильны.
> А если вместо ADOQuery.SQL.Text := SQLText; оставить закоментированную строку, то все работает...
тут и задана и определена одна переменная.

> и вообще как он названия определяет колонок?
прочитай в документации/книжке по sql. там все подробно.

и еще совет - 1: пиши понятнее, без мусора. 2: проверяй на простых, просто таки элементарных запросах. 3: используй ADODataSet.

← →
gedevan (2016-01-09 01:45) [7]

да сделал в запросе prm,prm2,prm3,prm4 и заработало. Хотя как по мне так это бред (я учился на 1С) ибо реально это один параметр, одно и тоже значение. почему его нужно разными то писать.

← →
sniknik © (2016-01-09 18:26) [8]

реально это 4-ре параметра, алиасы/одноименные не поддерживаются (как было в BDE например, хотя там это было типа полу ошибкой). а почему... ну так сделали, и не в дельфе, а в "API" там вообще вопросиками и по порядковому номеру назначаются... в дельфе хоть какое то удобство.
если хочешь можешь оставить как есть, одноименными, но значение задавать все одно всем, по индексу. тоже можно.

← →
Лори (2016-01-11 12:07) [9]

Несколько не мой подтип СУБД/Драйвер, но напишу мысли.

Почему не сделать DECLARE переменная, переменная равна параметру prm, в самом запросе использовать переменную?
Почему вообще не сделать просто VIEW в БД?

И с какого перепуга "одним запросом быстрее получится" из-за UNION? о___О
Это скорее индексы надо добавлять...

← →
sniknik © (2016-01-11 17:14) [10]

> Почему не сделать DECLARE переменная
Access

> И с какого перепуга "одним запросом быстрее получится"
при прочих равных, 1 запрос на 1000 записей, и 100 запросов по 10 записей... первый будет быстрее. ну и т.д. в пропорции.

> из-за UNION? о___О
из-за него как раз может притормозить, т.к. неявно distinct - сортировка делается, т.е. дополнительная обработка данных, т.что если этого не нужно лучше использовать UNION ALL.
хотя боюсь тормоза у него не из-за скорости выборки, а из-за его же собственного кода, как и здесь
http://delphimaster.net/view/2-1451827715/
sniknik © (03.01.16 18:58) [6]

← →
gedevan (2016-01-18 03:03) [11]

ну скорость выросла я б сказал в разы, после внедрения единого запроса.

← →
gedevan (2016-01-24 07:31) [12]

И все же при больших базах (1-2кк + записей) скорость выполнения запроса сильно падает. Вопрос если использовать paradox может стать быстрее? Или что нибудь еще? Вариант загружать все 600+ мб одним запросом что-то мне не нравится, да и сомневаюсь что по выборке он будет быстрее бегать...

Или может что то можно сделать с базой, чтобы запросы делались быстрее? Сейчас у меня несколько таблиц у них у всех есть одно поле по которому их можно связать, собственно это where ShotNumber = :prm";

Я ничего не индексирую не связываю, т.к. собственно и не знаю как, но стоит узнавать, будет ли от этого толк?

← →
sniknik © (2016-01-24 18:10) [13]

> загружать все 600+ мб одним запросом что-то мне не нравится
не поверишь, но это и не рекомендуется. загружать нужно только то что требуется.

> что то можно сделать с базой, чтобы запросы делались быстрее?
вот так абстрактно? ничего. и смена на другую тоже не поможет. разве если только случайно.

> но стоит узнавать, будет ли от этого толк?
научишься будет, нет значит нет.

← →
gedevan (2016-01-27 11:01) [14]

Еще одна проблема с этим запросом

поле FourSearch, есть только в первом запросе. В остальных я просто подставляю 0, а в таблице по которой делается запрос вообще нет такой колонки. И вот когда я через TADODataSet пытаюсь бегать по этой выборке и вызывать FourSearch := ADODataSet.FieldByName("FourSearch").Asboolean;

То он ругается что не может преобразовать ее к типу булеан! даже если я вызываю эту команду только в отношении строк которые взяты из таблицы Templates. И если этот датасет вывести в DBGrid то в этом поле будет значится 0 или -1. А если открыть в екселе, то ЛОЖЬ или ИСТИНА!

Чтобы такое сделать чтобы в DBGrid отображалось True,False ну и не ругалось чтобы оно?

На данный момент я нашел только одно решение, добавить во все таблицы это поле и забивать его каким то одним значением. А можно ли это решить на уровне этого запроса не добавляя ничего в таблицы?

← →
sniknik © (2016-01-27 11:14) [15]

> В остальных я просто подставляю
подставляй No, если дело в том о чем пишешь, а не в 17й строке, поможет. хотя... насколько помню 0 прекрасно преобразуется в буленовский тип.

← →
gedevan (2016-01-27 15:08) [16]

if TableExists(AConnection, "Templates") then begin result := result + "Select " + """Tem"","+ "ShotNumber," + "Name," + "ItemName," + "NumberOfSearch," + "tempfound,"+ "itIsTemplate," + "anotherTime," + "SearchFixed," + "x1," + "x2," + "y1," + "y2," + "ksov," + "searchtime," + "FourSearch," + "NameConnectedScreen," + "NULL," + "errorCode " + "FROM Templates where ShotNumber = :prm0"; inc(parQty); end; if TableExists(AConnection, "ColorSearch") then begin if result <> "" then result := result + " UNION "; result := result + "Select ""Col"",ShotNumber, " + "Name,""no items"",NumberOfSegment,tempfound,""No"",""No"",""No"",x1,x2,y1,y2,0,searchtime, " + """No"",NameConnectedScreen,NULL,errorCode FROM ColorSearch where ShotNumber = :prm2"; inc(parQty); end;

эм, сократил до 2х запросов, для теста. Поставил NO, теперь у меня в строках из темплейта 0 и -1 в dbgrid, а из таблицы ColorSearch показывает NO. А вот например поле tempfound, которое я добавил в ColorSearch и заполняю в таблице и для него для всех строк отражается true,false. Я хочу чтобы так все поля показывали (

← →
gedevan (2016-01-27 15:12) [17]

AConnection.Execute("CREATE TABLE ColorSearch " + "(_Index int IDENTITY(1,1),"+ "ShotNumber numeric(19,0)," + "Name char(50)," + "NumberOfSegment numeric(10,0),"+ "tempfound bit NULL DEFAULT 0, " + "x1 numeric(10,0)," + "x2 numeric(10,0)," + "y1 numeric(10,0)," + "y2 numeric(10,0)," + "searchtime numeric(10,0)," + "NameConnectedScreen char(50)," + " errorCode numeric(19,0)" + ");");

вот так выглядит реальная таблица ColorSearch.

← →
gedevan (2016-01-27 17:25) [18]

и ""false"" и ""no"" функцией преобразуется без ошибок. А DBGrid данные из таблицы Template показывает как 0 и -1 ( при этом.

← →
sniknik © (2016-01-27 18:33) [19]

> Поставил NO
вообще то ты поставил строку "No", а не No ...

← →
sniknik © (2016-01-27 18:51) [20]

> Я хочу чтобы так все поля показывали (
вычисляемые поля не имеют заголовка с типом, тип определяется в вариантном значении, а оно в зависимости от выражения (движком)...
еще вот попробуй длинну строки в поле получить определенную, ну вот ты знаешь что результирующая строка длинной не больше 10 символов, а выражение даже сложение пустых - ""+"" даст строку пустую конечно же но в поле длинной 255... вроде бы, не помню. проверь.

← →
gedevan (2016-01-27 18:53) [21]

да, но она преобразование проходит. А просто No нет.

И еще вопрос как в TADODataset узнать имя поля в выборке? ибо DBGrid требует указывать имя поля, которого я не знаю ( т.к. там что то вроде Expr1000

← →
gedevan (2016-01-27 19:11) [22]

ADOQuery.Fields[16].FieldName

← →
gedevan (2016-01-27 19:34) [23]

прочитал мануал по sql но так и не нашел. Неужели нет возможности вставить конструкцию типа if value = 1 then ""true"" ну то есть, если значение поля в запросе равно единице то вернуть вместо единицы тру?

← →
Кщд © (2016-01-28 06:51) [24]

iif/switch
в гугле забанили?

← →
кгшзх © (2016-01-28 08:57) [25]

здесь применена другая мощная методика программирования.
по рандому перебираем знакомые и не очень слова и составляем из них программу.
однажды должно получится.

← →
sniknik © (2016-01-28 10:38) [26]

> А просто No нет.
а у тебя реально access?

Boolean data type
A True/False or yes/no value. Boolean values are usually stored in Bit fields in a Microsoft Jet database; however, some databases don"t support this data type directly.

> если значение поля в запросе равно единице то вернуть вместо единицы тру?
somefield * -1
если, как я понял, у тебя там "извращенное значение типа" - 0 = false, 1 = true.

> однажды должно получится
есть теория, что миллион обезьян случайно нажимая клавиши печатной машинки могут написать "войну и мир"... опровергнута изобретением интернета.

← →
sniknik © (2016-01-28 10:39) [27]

> А просто No нет.
выполни запрос -
SELECT yes, no

← →
gedevan (2016-01-28 11:06) [28]

да, забанили ( про свич я ничего не нашел. А Case when then else end выдает ошибку

← →
gedevan (2016-01-28 11:14) [29]

на NO он не ругается и запрос выполняет. Но FourSearch := ADODataSet.FieldByName("FourSearch").Asboolean; вот это выражение выдает ошибку, что не может преобразовать. А если вставить строку то не выдает. В общем эту проблему я можно сказать решил, меня это устраивает.

Не устраивает же что DBGrid мне показывает 0 и -1 (вместо false, true)для значений из таблицы Templates (где я не просто нарисовал эти поля в запросе, а они реально есть в таблице Templates). Вот я и хочу подменить их также на строки прям в запросе

← →
sniknik © (2016-01-28 11:16) [30]

> про свич я ничего не нашел
ищи в справке акссса, гугль не нужен!

← →
gedevan (2016-01-28 11:21) [31]

somefield * -1 если, как я понял, у тебя там "извращенное значение типа" - 0 = false, 1 = true.

на -1 отказывается умножать, вылетает с ошибкой запрос. Если убрать запросы с фиктивными полями, то DBGrid показывает True и False как и задумано. Причем я точно уверен что в запросе 0 и 1, где DBGrid берет -1 я понятия не имею.

← →
sniknik © (2016-01-28 11:22) [32]

> вот это выражение выдает ошибку, что не может преобразовать.

> sniknik © (27.01.16 11:14) [15]
> > В остальных я просто подставляю
> подставляй No, если дело в том о чем пишешь, а не в 17й строке, поможет. хотя... насколько помню 0 прекрасно преобразуется в буленовский тип.
предположение про 17ю строку обретает смысл.

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

> на -1 отказывается умножать, вылетает с ошибкой запрос.
значит ты врешь, что у тебя accecc, либо "творчески" интерпретируешь мною сказанное и скрываешь инфу. (ошибка/как делаешь/чего этим добиваешься)
похоже проект секретный... "умываю руки".

← →
gedevan (2016-01-28 11:32) [34]

"Switch (FourSearch<>0, ""true"", FourSearch=0, ""false""),"

вот это в запрос вставлять? ошибку выдает (

← →
gedevan (2016-01-28 11:35) [35]

чего секретный то, я ж показывал запрос

Function GetPhotoQueryText(AConnection: TADOConnection; var parQty: integer): string; begin result := ""; parQty := 0; if TableExists(AConnection, "Templates") then begin result := result + "Select " + """Tem"","+ "ShotNumber," + "Name," + "ItemName," + "NumberOfSearch," + "tempfound,"+ "itIsTemplate, "+ "anotherTime, "+ "SearchFixed, "+ "x1," + "x2," + "y1," + "y2," + "ksov," + "searchtime," + "FourSearch*(-1), "+ "NULL," + "errorCode " + "FROM Templates where ShotNumber = :prm0"; inc(parQty); end; //if result <> "" then // result := result + ";"; // exit; if TableExists(AConnection, "ColorSearch") then begin if result <> "" then result := result + " UNION "; result := result + "Select ""Col"",ShotNumber, " + "Name,""no items"",0,tempfound,""false"",""false"",""false"",x1,x2,y1,y2,0,searchtime," + """false"",NULL,errorCode FROM ColorSearch where ShotNumber = :prm2"; inc(parQty); end; if TableExists(AConnection, "DynObj") then begin if result <> "" then result := result + " UNION "; result := result + "Select " + """Dyn""," + "ShotNumber, " + "Name," + """no items""," + "NumberOfSegment," + "tempfound," + """false""," + """false""," + """false""," + "x1," + "x2," + "y1," + "y2," + "0," + "searchtime," + """false""," + "NULL," + "errorCode" + " FROM DynObj where ShotNumber = :prm1"; inc(parQty); end; if TableExists(AConnection, "TextRecognize") then begin if result <> "" then result := result + " UNION "; result := result + "Select ""Tex"",ShotNumber, " + "Name,""no items"",NumberOfSegment,tempfound,""false"",""false"",""false"",x1,x2,y1,y2,0,se archtime," + """false"",RecognizedText,errorCode FROM TextRecognize where ShotNumber = :prm3"; inc(parQty); end; if result <> "" then result := result + ";"; end;

← →
gedevan (2016-01-28 11:42) [36]

ошибка при умножении возникала в другом месте..это не в запросе

← →
gedevan (2016-01-28 11:52) [37]

свич тоже работает...одна проблема, DBGrid тогда вообще ничего не показывает.

В общем если делать через дбгрид (что я уже пытался, но не очень успешно). То вопрос.

Если я с одной стороны подключаю TADODataSet к DBGrid, а с другой бегаю циклам по выборке TADODataSet - это правильно или будет вызывать проблемы? (потому что у меня DBGrid глючит при попытках в нем что либо перерисовывать).

procedure TTv.TVTableLogDrawColumnCell(Sender: TObject; const Rect: TRect; DataCol: integer; Column: TColumn; State: TGridDrawState); begin if ADODataSet1.FieldByName("tempfound").Value = true then Font.Color := clGreen else if ADODataSet1.FieldByName("tempfound").Value = false then Font.Color := clred; if (Column.FieldName = "Expr1000") or (Column.FieldName = "Name") or (Column.FieldName = "ItemName") then begin with TVTableLog.Canvas do begin Brush.Color := clwhite; FillRect(Rect); TextOut(Rect.Left + 2, Rect.Top + 2, Column.Field.Text); end; end else begin with TVTableLog.Canvas do begin Brush.Color := clwhite; FillRect(Rect); TextOut(Rect.Right - 2 - TextWidth(Column.Field.Text), Rect.Top + 2, Column.Field.Text) end; end; end;

> "Switch (FourSearch<>0, ""true"", FourSearch=0, ""false""),"
> вот это в запрос вставлять?
все зависит от контекста КАК ИМЕННО вставлять, и для чего. вставить к примеру палку в колесо велосипеда на ходу, или на стоянке разные вещи. чтобы велосипед остановить/он не поехал или подогнуть спицы тоже.

> ошибку выдает (
НЕ БЫВАЕТ ОШИБОК "ошибку выдает". в ошибке обычно ИНФОРМАЦИЯ о том что происходит.

> чего секретный то, я ж показывал запрос
т.е. мы уже столько общаемся а запрос не поменялся вообще никак... смысл тогда?

← →
gedevan (2016-01-28 12:49) [39]

НЕ БЫВАЕТ ОШИБОК "ошибку выдает". в ошибке обычно ИНФОРМАЦИЯ о том что происходит.

ошибка аксес виолейшн. Выдается если не закомментировать при этом функцию из [37]. Где конкретно выдается понять так и не удалось...т.к. это происходит на какой то 100500 итерации или я вообще хз где.

← →
gedevan (2016-01-28 13:02) [40]

я понял он вылетает в Column.Field.Text этом месте когда Column.FieldName = "FourSearch", Т.е. туда должно было быть помещено значение помноженное на -1 или свичем полученное

← →
gedevan (2016-01-28 13:05) [41]

т.к. Column.Field = nil тогда (

> ошибка аксес виолейшн.
пятая дельфя и adoquery используешь?
http://www.delphisources.ru/pages/faq/base/ado_acc_viol_err.html

> т.к. Column.Field = nil тогда (
как у булена с дефзначением вдруг значение nil???
+
AsBoolean должно преобразовывать в фалсе.

← →
gedevan (2016-01-28 13:28) [44]

Делфи xe5 AdoDataSet использую.

В общем я сделал перерисовывание в ДБГриде. Но есть одна беда. Переодически после очередного запроса DBGrid начинает непрерывно и без остановки вызывать функцию (запрос при этом не вызывается вновь, т.е. оно "само")

procedure TTv.TVTableLogDrawColumnCell(Sender: TObject; const Rect: TRect; DataCol: integer; Column: TColumn; State: TGridDrawState); var t: string; begin if ADODataSet1.FieldByName("tempfound").Value = true then Font.Color := clGreen else if ADODataSet1.FieldByName("tempfound").Value = false then Font.Color := clred; if (Column.FieldName = "Expr1000") or (Column.FieldName = "Name") or (Column.FieldName = "ItemName") then begin with TVTableLog.Canvas do begin Brush.Color := clwhite; if Column.Field <> nil then begin FillRect(Rect); TextOut(Rect.Left + 2, Rect.Top + 2, Column.Field.Text); end; end; end else begin with TVTableLog.Canvas do begin Brush.Color := clwhite; if Column.Field <> nil then begin t := Column.Field.Text; if (Column.FieldName = "FourSearch") or (Column.FieldName = "SearchFixed") or (Column.FieldName = "anotherTime") then begin if t = "-1" then t := "True" else if t = "0" then t := "False"; end; FillRect(Rect); TextOut(Rect.Right - 2 - TextWidth(t), Rect.Top + 2, t); end; end; end; end;

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

← →
gedevan (2016-01-28 13:43) [45]

глюк случается если в одной колонке в разных ячейках выполняются оба этих условия(т.е. есть и tempfound = true и tempfound = false в колонке):

if ADODataSet1.FieldByName("tempfound").Value = true then Font.Color := clGreen; //else if ADODataSet1.FieldByName("tempfound").Value = false then // Font.Color := clred;

и вот если например, второе закомментировать, то глюка нет. А как тогда раскрасить в разные цвета? (

← →
gedevan (2016-01-29 08:46) [46]

разобрался TVTableLog.Canvas.Font.Color := clGreen; нужно писать

Как сделать sql запрос к нескольким таблицам (ADO,MS Access)? Найти похожие ветки