Използване на заявка за обединение, за да се комбинират няколко заявки в една

В примера от предишния раздел, използващ базата данни Northwind, се комбинират данни само от две таблици. Можете обаче много лесно да комбинирате три или повече таблици в заявка за обединение. Например като използвате предишния пример, бихте могли да включите в резултата от заявката и имената на Служители. Може да изпълните тази задача, като добавите трета заявка и да я комбиниране с предишната SQL команда с допълнителна ключова дума UNION по следния начин:

SELECT Customers.Company, Customers.[Last Name], Customers.[First Name]
FROM Customers

UNION

SELECT Suppliers.Company, Suppliers.[Last Name], Suppliers.[First Name]
FROM Suppliers

UNION

SELECT Employees.Company, Employees.[Last Name], Employees.[First Name]
FROM Employees

ORDER BY [Last Name], [First Name];

Когато покажете резултата в изглед на лист с данни, ще бъдат изброени всички служители с примерното име на фирма, което вероятно не е много полезно. Ако искате това поле да показва дали даден човек е служител от фирмата или от доставчик или клиент, можете да включите фиксирана стойност вместо името на фирмата. Ето как би изглеждала SQL командата:

SELECT "Customer" As Employment, Customers.[Last Name], Customers.[First Name]
FROM Customers

UNION

SELECT "Supplier" As Employment, Suppliers.[Last Name], Suppliers.[First Name]
FROM Suppliers

UNION

SELECT "In-house" As Employment, Employees.[Last Name], Employees.[First Name]
FROM Employees

ORDER BY [Last Name], [First Name];

Ето как изглежда резултатът в изглед на лист с данни. Access показва следните пет примерни записа:

Горната заявка може да се опрости още повече, тъй като Access чете в заявката за обединение само имената на изходните полета от първата заявка. Тук виждате, че сме премахнали резултата от разделите по втората и третата заявка:

SELECT "Customer" As Employment, [Last Name], [First Name]
FROM Customers

UNION

SELECT "Supplier", [Last Name], [First Name]
FROM Suppliers

UNION

SELECT "In-house", [Last Name], [First Name]
FROM Employees

ORDER BY [Last Name], [First Name];

В една заявка за обединение на Access подреждането се разрешава само веднъж, но всяка заявка може да се филтрира поотделно. Въз основа на заявката за обединение от предишния раздел, предлагаме ви пример, в който сме филтрирали всяка заявка чрез добавяне на клаузата WHERE.

SELECT "Customer" As Employment, Customers.[Last Name], Customers.[First Name]
FROM Customers
WHERE [State/Province] = "UT"

UNION

SELECT "Supplier", [Last Name], [First Name]
FROM Suppliers
WHERE [Job Title] = "Sales Manager"

UNION

SELECT "In-house", Employees.[Last Name], Employees.[First Name]
FROM Employees
WHERE City = "Seattle"

ORDER BY [Last Name], [First Name];

Превключете към изглед на лист с данни, и ще видите резултат, подобен на този:

Ако заявките за обединение са много различни, може да се сблъскате със ситуация, при която изходното поле трябва да комбинира данни от различни типове. Ако е така, заявката за обединение най-често ще върне резултатите като текстови данни тъй като този тип данни може да съдържа както текст, така и числа.

За да разберете как работи това, ще използваме заявката за обединение Транзакции на продукти в примерната база данни Northwind. Отворете тази примерна база данни и след това отворете заявката "Транзакции на продукти" в изглед на лист с данни. Последните десет записа трябва да бъдат подобни на този резултат:

Да предположим, че искате полето "Количество" да бъде разделено на две – "Покупка" и "Продажба". Също така да предположим, че искате да има фиксирана стойност 0 в полетата, в които няма стойност. Ето как ще изглежда SQL за тази заявка за обединение:

SELECT [Product ID], [Order Date], [Company Name], [Transaction], 0 As Buy, [Quantity] As Sell
FROM [Product Orders]

UNION

SELECT [Product ID], [Creation Date], [Company Name], [Transaction], [Quantity] As Buy, 0 As Sell
FROM [Product Purchases]

ORDER BY [Order Date] DESC; 

Ако превключите към изглед на лист с данни, ще видите, че последните десет записа сега изглеждат по следния начин:

Продължавайки с този пример, какво би станало, ако искате полетата с нула да бъдат празни? Можете да модифицирате SQL командата, така че да не се показва нищо вместо да се показва нула, като добавите ключовата дума Null както следната:

SELECT [Product ID], [Order Date], [Company Name], [Transaction], Null As Buy, [Quantity] As Sell
FROM [Product Orders]

UNION

SELECT [Product ID], [Creation Date], [Company Name], [Transaction], [Quantity] As Buy, Null As Sell
FROM [Product Purchases]

ORDER BY [Order Date] DESC;

Тъй като обаче може да се случи превключване към изглед на лист с данни, сега имате неочакван резултат. В колоната "Закупуване" се изчистват всички полета:

Причината това да се случи е, че Access определя типовете данни на полетата от първата заявка. В този пример Null не е число.

Така че какво ще се случи, ако опитате да вмъкнете празен низ като празна стойност на поле? SQL командата за този опит може да изглежда подобно на следната:

SELECT [Product ID], [Order Date], [Company Name], [Transaction], "" As Buy, [Quantity] As Sell
FROM [Product Orders]

UNION

SELECT [Product ID], [Creation Date], [Company Name], [Transaction], [Quantity] As Buy, "" As Sell
FROM [Product Purchases]

ORDER BY [Order Date] DESC;

Когато превключите към изглед на лист с данни, ще видите, че Access извлича стойностите за "Закупуване", но конвертира тези стойности в текст. Можете да кажете, че това са текстови стойности, тъй като те са подравнени отляво, в изглед на лист с данни. Празният низ в първата заявката не е число и затова виждате такива резултати. Ще забележите, че стойностите на продажба също се конвертират в текст, тъй като записите за покупка съдържат празен низ.

И така, как да решите този проблем?

Решението е да принудите заявката да очаква стойността на полето да бъде число. Това може да се извърши с израза:

IIf(False, 0, Null)

Условието, което проверявате, False, никога няма да бъде True, следователно изразът винаги ще връща Null, но Access все още изчислява и двете числови опции и решава, че резултатът е число или Null.

Ето как да използвате този израз в нашия работен пример:

SELECT [Product ID], [Order Date], [Company Name], [Transaction], IIf(False, 0, Null) As Buy, [Quantity] As Sell
FROM [Product Orders]

UNION

SELECT [Product ID], [Creation Date], [Company Name], [Transaction], [Quantity] As Buy, Null As Sell
FROM [Product Purchases]

ORDER BY [Order Date] DESC;

Обърнете внимание, че не е необходимо да модифицирате втората заявка.

Ако превключите към изглед на лист с данни, вече ще видите резултата, който искаме:

Може да постигнете същия резултат по друг начин, като добавите още една заявка в началото на заявките от заявката за обединение:

SELECT 
    0 As [Product ID], Date() As [Order Date], 
    "" As [Company Name], "" As [Transaction], 
    0 As Buy, 0 As Sell
FROM [Product Orders]
WHERE False

За всяко поле Access ви връща фиксирани стойности с типа данни, зададен от вас. Разбира се, че не искате резултатът от тази заявка да пречи на другите резултати, така че трикът за избягване на това е да включите клаузата WHERE към False:

WHERE False

Това е малък трик, тъй като това винаги е неистина, така че заявката не връща нищо. Като комбинираме тази команда със съществуващата SQL команда, стигаме до окончателната команда, както следва:

SELECT 
    0 As [Product ID], Date() As [Order Date], 
    "" As [Company Name], "" As [Transaction], 
    0 As Buy, 0 As Sell
FROM [Product Orders]
WHERE False

UNION

SELECT [Product ID], [Order Date], [Company Name], [Transaction], Null As Buy, [Quantity] As Sell
FROM [Product Orders]

UNION

SELECT [Product ID], [Creation Date], [Company Name], [Transaction], [Quantity] As Buy, Null As Sell
FROM [Product Purchases]

ORDER BY [Order Date] DESC;

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

Ето друг пример, който можете да създадете в примерната база данни Northwind, за да илюстрирате как да получите общата сума в заявка за обединение.

1. Създайте нова проста заявка, за да покажете закупуването на бира (ИД на продукт = 34 в базата данни Northwind) с помощта на следния SQL синтаксис:

   SELECT [Purchase Order Details].[Date Received], [Purchase Order Details].Quantity
   FROM [Purchase Order Details]
   WHERE ((([Purchase Order Details].[Product ID])=34))
   
   ORDER BY [Purchase Order Details].[Date Received];

2. Превключете към изглед на лист с данни, и би трябвало да видите четири покупки:

   Дата на получаване	Количество
   22.1.2006 г.	100
   22.1.2006 г.	60
   4.4.2006 г.	50
   5.4.2006 г.	300

3. За да получите общата сума, създайте проста заявка за събиране с помощта на следната SQL команда:

   SELECT Max([Date Received]), Sum([Quantity]) AS SumOfQuantity
   FROM [Purchase Order Details]
   WHERE ((([Purchase Order Details].[Product ID])=34))

4. Превключете към изглед на лист с данни, и би трябвало да видите само една покупка:

   ПоследнаДата на получаване	СумаНаКоличествата
   5.4.2006 г.	510

5. Комбинирайте тези две заявки в заявка за обединение, за да добавите записа с общото количество към записите за покупка:

   SELECT [Purchase Order Details].[Date Received], [Purchase Order Details].Quantity
   FROM [Purchase Order Details]
   WHERE ((([Purchase Order Details].[Product ID])=34))
   
   UNION
   
   SELECT Max([Date Received]), Sum([Quantity]) AS SumOfQuantity
   FROM [Purchase Order Details]
   WHERE ((([Purchase Order Details].[Product ID])=34))
   
   ORDER BY [Purchase Order Details].[Date Received];

6. Превключете към изглед на лист с данни, и би трябвало да видите четирите покупки със сумата на всяка от тях, последвани от запис с общата сума на количеството:

   Дата на получаване	Количество
   22.1.2006 г.	60
   22.1.2006 г.	100
   4.4.2006 г.	50
   5.4.2006 г.	300
   5.4.2006 г.	510

Това обхваща основите на добавянето на общи суми в заявка за обединение. Може и да се наложи да включите фиксирани стойности и в двете заявки – например "Подробни данни" и "Общо", за да отделите визуално записа с общата сума от другите записи. Можете да прегледате чрез фиксирани стойности в раздел Комбиниране на три или повече таблици или заявки в заявка за обединение.

Заявките за обединение в Access по подразбиране включват само отделни записи. Какво обаче става, ако искате да включите всички записи? Тук може да бъде полезен друг пример.

В предишния раздел ви показахме как да създадете обща сума в заявка за обединение. Променете SQL командата за тази заявка за обединение, така че да включва ИД на продукт = 48:

SELECT [Purchase Order Details].[Date Received], [Purchase Order Details].Quantity
FROM [Purchase Order Details]
WHERE ((([Purchase Order Details].[Product ID])=48))

UNION

SELECT Max([Date Received]), Sum([Quantity]) AS SumOfQuantity
FROM [Purchase Order Details]
WHERE ((([Purchase Order Details].[Product ID])=48))

ORDER BY [Purchase Order Details].[Date Received];

Превключете към изглед на лист с данни, и би трябвало да видите един малко заблуждаващ резултат:

Един запис естествено не връща два пъти количеството на общата сума.

Причината да виждате този резултат е, че в един ден едно и също количество шоколадови бонбони е продадено два пъти – както е записано в таблицата "Подробни данни за поръчка за покупка". Ето резултат от проста заявка за избиране, който показва и двата записа в примерната база данни Northwind:

В заявката за обединение, посочена по-рано, ще видите че полето "ИД на поръчка за покупка" не е включено и че двете полета не представляват два отделни записа.

Ако искате да включите всички записи, използвайте UNION ALL вместо UNION във вашата SQL команда. Това най-вероятно ще окаже влияние върху сортирането на резултатите, така че може да се наложи да включите клаузата ORDER BY, за да определите реда на сортиране. Ето е променената SQL команда, съставена въз основа на предишния пример:

SELECT [Purchase Order Details].[Date Received], Null As [Total], [Purchase Order Details].Quantity
FROM [Purchase Order Details]
WHERE ((([Purchase Order Details].[Product ID])=48))

UNION ALL

SELECT Max([Date Received]), "Total" As [Total], Sum([Quantity]) AS SumOfQuantity
FROM [Purchase Order Details]
WHERE ((([Purchase Order Details].[Product ID])=48))

ORDER BY [Total];

Превключете към изглед на лист с данни, и би трябвало да видите всички подробности в допълнение към общата сума като последен запис:

Разпространено използване на заявка за обединение е да служи като източник на записи за контрола на разгъващ се списък за формуляр. Може да използвате този разгъващ се списък, за да изберете стойност за филтриране на записите от формуляра. Например филтриране на записите за служителите по техните градове.

За да видите как действа това, ви предлагаме друг пример, който може да създадете в примерната база данни Northwind, за да илюстрирате този сценарий.

1. Създайте проста заявка за избиране, като използвате следния SQL синтаксис:

   SELECT Employees.City, Employees.City AS Filter
   FROM Employees;

2. Превключете към изглед на лист с данни, и би трябвало да видите следните резултати:

   Град	Филтър
   Сиатъл	Сиатъл
   Белвю	Белвю
   Редмънд	Редмънд
   Къркланд	Къркланд
   Сиатъл	Сиатъл
   Редмънд	Редмънд
   Сиатъл	Сиатъл
   Редмънд	Редмънд
   Сиатъл	Сиатъл

3. Ако погледнете тези резултати, може да не получите голяма полза. Разгънете обаче тази заявка и я превърне в заявка за обединение с помощта на следната SQL команда:

   SELECT Employees.City, Employees.City AS Filter
   FROM Employees
   
   UNION
   
   SELECT "<All>", "*" AS Filter
   FROM Employees
   
   ORDER BY City;

4. Превключете към изглед на лист с данни, и би трябвало да видите следните резултати:

   Град	Филтър
   <Всички>	*
   Белвю	Белвю
   Къркланд	Къркланд
   Редмънд	Редмънд
   Сиатъл	Сиатъл

   Access извършва обединение на деветте записа, показани по-рано, с фиксирани полета стойности <Всички> и "*".

   Тъй като тази клауза за обединение не съдържа UNION ALL, Access връща само различните записи, което означава, че всеки град се връща само веднъж с фиксирани еднакви стойности.

5. Когато вече имате изпълнена заявка за обединение, показваща всяко име на град само веднъж, заедно с опция, която по същество избира всички градове, можете да използвате тази заявка като източник на записи за разгъващ се списък във формуляр. Като използвате този конкретен пример като модел, можете да създадете контрола на разгъващ се списък за формуляр, като зададете тази заявка като източник на записи за него, да зададете свойството "Ширина на колоната" за колоната "Филтър" да бъде 0 (нула), за да я скриете визуално, и след това да зададете свойството "Обвързана колона" да бъде 1, за да укажете индекса на втората колона. В свойството "Филтър" на самия формуляр можете да добавите код – например като следния, за да активирате филтър за формуляра с помощта на стойността на това, което е избрано в контролата на разгъващия се списък:

   Me.Filter = "[City] Like '" & Me![FilterComboBoxName].Value & "'"
   Me.FilterOn = True

   Потребителят на формуляра може след това да филтрира записите от формуляра с име на определен град или да избере <Всички> за да бъде изведен списъкът на всички записи за всички градове.

Най-горе на страницата