„Union“ užklausos naudojimas norint sujungti kelių užklausų rezultatus į vieną

Ankstesniame skyriuje pateiktame pavyzdyje naudojant „Northwind“ duomenų bazę duomenys sujungiami tik iš dviejų lentelių. Tačiau „union“ užklausoje labai lengvai galite sujungti tris ar daugiau lentelių. Pavyzdžiui, tęsiant ankstesnį pavyzdį, į užklausos išvestį taip pat galite įtraukti darbuotojų vardus. Tai galite padaryti įtraukdami trečią užklausą ir ją sujungdami su ankstesniu SQL sakiniu naudodami papildomą raktažodį UNION, kaip parodyta čia:

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];

Peržiūrint rezultatą duomenų lapo rodinyje, visi darbuotojai bus pateikti su tuo pačiu įmonės pavadinimu, o tai tikriausiai nėra labai naudinga. Jei norite, kad lauke būtų nurodoma, ar asmuo yra vietinis darbuotojas, tiekėjo darbuotojas ar kliento darbuotojas, vietoj įmonės pavadinimo galite įtraukti fiksuotą reikšmę. Štai kaip atrodytų 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];

Štai kaip rezultatas rodomas duomenų lapo rodinyje. „Access“ rodo šiuos penkis įrašų pavyzdžius.

Pirmiau pateiktą užklausą galima sumažinti dar labiau, kadangi „Access“ skaito tik pirmos užklausos „union“ užklausoje išvesties laukų vardus. Čia matote, jog pašalinome išvestį iš antros ir trečios užklausų sekcijų:

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“ „union“ užklausoje rikiavimas leidžiamas tik vieną kartą, bet kiekvieną užklausą galima filtruoti atskirai. Remiantis ankstesniame skyriuje pateikta „union“ užklausa, štai pavyzdys, kaip mes filtravome kiekvieną užklausą įtraukdami sąlygą 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];

Įjunkite duomenų lapo rodinį ir pamatysite rezultatus, panašius į šiuos:

Jei „union“ užklausos labai skiriasi, galite susidurti su situacija, kai išvesties laukas turi sujungti skirtingų tipų duomenis. Jei taip nutinka, „union“ užklausa dažniausiai grąžins teksto duomenų tipo rezultatus, kadangi šis duomenų tipas gali apimti ir tekstą, ir skaičius.

Kad suprastumėte, kaip tai veikia, naudosime „union“ užklausą Produkto operacijos „Northwind“ duomenų bazės pavyzdyje. Atidarykite tą duomenų bazės pavyzdį, tada atidarykite užklausą Produkto operacijos duomenų lapo rodinyje. Paskutinieji dešimt įrašų turėtų būti panašūs į šią išvestį:

Tarkime, kad norite išskaidyti lauką Kiekis į dvi dalis – pirkimas ir pardavimas. Taip pat laukuose, kuriuose nėra reikšmės, norite turėti fiksuotą nulinę reikšmę. Štai kaip atrodys šios „union“ užklausos 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; 

Įjungus duomenų lapo rodinį, paskutinieji dešimt įrašų atrodys taip:

Tęsiant šį pavyzdį, ką daryti, jei norite, kad laukai, kuriuose yra nulinės reikšmės, būtų tušti? Galite modifikuoti SQL, kad užuot rodžius nulį būtų nerodoma nieko, įtraukdami raktažodį Null, kaip parodyta čia:

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;

Tačiau, kaip galbūt pastebėjote įjungę duomenų lapo rodinį, dabar turite netikėtą rezultatą. Stulpelyje Pirkimas visi laukai išvalyti:

Taip atsitinka todėl, kad „Access“ nustato laukų duomenų tipus pagal pirmą užklausą. Šiame pavyzdyje Null (tuščia reikšmė) nėra skaičius.

Taigi kas nutiks, jei pabandysite įterpti tuščią eilutę į laukų tuščią reikšmę? Šio bandymo SQL rezultatas gali atrodyti taip:

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;

Įjungę duomenų lapo rodinį, pamatysite, kad „Access“ gauna Pirkimo reikšmes, bet jas konvertavo į tekstą. Galite nustatyti, kad tai tekstinės reikšmės, nes duomenų lapo rodinyje jos lygiuojamos kairėje. Tuščia eilutė pirmoje užklausoje nėra skaičius, todėl ir matote tokius rezultatus. Taip pat pastebėsite, kad Pardavimo reikšmės irgi konvertuotos į tekstą, nes pirkimo įrašuose yra tuščia eilutė.

Kaip išspręsti šią problemą?

Vienas iš sprendimų yra priversti užklausą tikėtis, kad lauko reikšmė bus skaičius. Tai galima atlikti naudojant reiškinį:

IIf(False, 0, Null)

Tikrintina sąlyga, False (klaidinga), niekada nebus True (teisinga), todėl reiškinys visada grąžins Null (tuščią reikšmę), bet „Access“ vis tiek įvertina abi išvesties parinktis ir nusprendžia, ar išvestis bus skaitinė, ar Null.

Štai kaip galime naudoti šį reiškinį mūsų pavyzdyje:

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;

Atkreipkite dėmesį, kad antrosios užklausos modifikuoti nereikia.

Jei įjungsite duomenų lapo rodinį, matysite rezultatą, kurio mums reikia:

Kitas būdas pasiekti tą patį rezultatą yra prieš užklausas, esančias „union“ užklausoje, pridėti dar vieną užklausą:

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

Kiekviename lauke „Access“ grąžina jūsų nustatyto duomenų tipo fiksuotas reikšmes. Žinoma, turbūt nenorite, kad šios užklausos išvestis kliudytų rezultatams, todėl norėdami to išvengti įtraukite sąlygą WHERE, kurios reikšmė yra False:

WHERE False

Tai nedidelė gudrybė, nes rezultatas visada yra False ir užklausa nieko negrąžina. Sujungę šį sakinį su esamu SQL, gauname užbaigtą sakinį:

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;

Specialus „union“ užklausos naudojimo atvejis yra sujungti įrašų rinkinį su vienu įrašu, kuriame yra vieno arba daugiau laukų suma.

Štai kitas pavyzdys, kurį galite sukurti „Northwind“ duomenų bazės pavyzdyje, kad suprastumėte, kaip gauti sumą „union“ užklausoje.

1. Sukurkite naują paprastą užklausą, kad peržiūrėtumėte alaus (produkto ID „Northwind“ duomenų bazėje yra 34) pirkimus naudodami šią SQL sintaksę:

   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. Įjunkite duomenų lapo rodinį ir turėtumėte matyti keturis pirkimus:

   Gavimo data	Kiekis
   2006-01-22	100
   2006-01-22	60
   2006-04-04	50
   2006-04-05	300

3. Norėdami gauti sumą, sukurkite paprastą agregavimo užklausą naudodami šį SQL:

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

4. Įjunkite duomenų lapo rodinį ir turėtumėte tik vieną įrašą:

   Gavimo datos didžiausia reikšmė	Kiekių suma
   2006-04-05	510

5. Sujunkite šias dvi užklausas į „union“ užklausą, kad prie pirkimo įrašų pridėtumėte bendro kiekio įrašą:

   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. Įjunkite duomenų lapo rodinį ir turėtumėte matyti keturis pirkimus ir jų sumą, o po jų – įrašą, kuris susumuoja kiekius.

   Gavimo data	Kiekis
   2006-01-22	60
   2006-01-22	100
   2006-04-04	50
   2006-04-05	300
   2006-04-05	510

Tai visi sumų įtraukimo į „union“ užklausą pagrindai. Taip pat galite įtraukti fiksuotas reikšmes abiejose užklausose, pvz., „išsami informacija“ ir „suma“, kad vizualiai atskirtumėte sumos įrašą nuo kitų įrašų. Galite apžvelgti fiksuotų reikšmių naudojimą skyriuje Trijų arba daugiau lentelių sujungimas „union“ užklausoje.

Programoje „Access“ „Union“ užklausose pagal numatytuosius parametrus yra tik unikalūs įrašai. Tačiau ką daryti, jei norite įtraukti visus įrašus? Čia gali praversti kitas pavyzdys.

Ankstesniame skyriuje parodėme jums, kaip „union“ užklausoje sukurti sumą. Modifikuokite tą „union“ užklausos SQL įtraukdami Product ID= 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];

Įjungę duomenų lapo rodinį turėtumėte matyti šiek tiek klaidinantį rezultatą:

Žinoma, vienas įrašas negrąžina dvigubo kiekio sumos.

Šio rezultato priežastis yra ta, kad vieną dieną toks pats šokolado kiekis buvo parduotas du kartus – tai įrašyta lentelėje Pirkimo užsakymo informacija. Štai paprasta išrinkimo užklausa, parodanti abu įrašus „Northwind“ duomenų bazės pavyzdyje:

Pirmiau paminėtoje „union“ užklausoje galite matyti, kad laukas Pirkimo užsakymo ID nėra įtrauktas, o abu laukai nesudaro dviejų unikalių įrašų.

Jei norite įtraukti visus įrašus, SQL sakinyje naudokite UNION ALL, o ne UNION. Tai tikriausiai turės įtakos rezultatų rikiavimui, todėl taip pat galite įtraukti sąlygą ORDER BY, kad nustatytumėte rikiavimo tvarką. Štai modifikuotas SQL, paremtas ankstesniu pavyzdžiu:

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];

Įjungę duomenų lapo rodinį turėtumėte matyti visą informaciją bei sumą kaip paskutinį įrašą.

„Union“ užklausos yra dažnai naudojamos kaip formos pasirinktinio įvedimo lauko valdiklio įrašų šaltinis. Galite naudoti tą pasirinktinio įvedimo lauką, kad pasirinktumėte reikšmę ir filtruotumėte formos įrašus. Pavyzdžiui, galite filtruoti darbuotojų įrašus pagal jų miestą.

Kad suprastumėte, kaip tai veikia, pateikiame kitą pavyzdį, kurį galite sukurti „Northwind“ duomenų bazės pavyzdyje, paaiškinantį šį scenarijų.

1. Sukurkite paprastą išrinkimo užklausą naudodami šią SQL sintaksę:

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

2. Įjunkite duomenų lapo rodinį ir turėtumėte matyti šiuos rezultatus:

   Miestas	Filtras
   Šiauliai	Šiauliai
   Rokiškis	Rokiškis
   Raseiniai	Raseiniai
   Kaunas	Kaunas
   Šiauliai	Šiauliai
   Raseiniai	Raseiniai
   Šiauliai	Šiauliai
   Raseiniai	Raseiniai
   Šiauliai	Šiauliai

3. Žvelgiant į šiuos rezultatus, neatrodo, kad jie naudingi. Išplėskite užklausą ir pertvarkykite ją į „union“ užklausą naudodami šį SQL:

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

4. Įjunkite duomenų lapo rodinį ir turėtumėte matyti šiuos rezultatus:

   Miestas	Filtras
   <All>	*
   Rokiškis	Rokiškis
   Kaunas	Kaunas
   Raseiniai	Raseiniai
   Šiauliai	Šiauliai

   „Access“ sukuria „union“ užklausą iš anksčiau pateiktų devynių įrašų, kurių fiksuotos reikšmės yra <All> (visi) ir „*“.

   Kadangi šioje „union“ sąlygoje nėra UNION ALL, „Access“ grąžina tik unikalius įrašus, o tai reiškia, kad kiekvienas miestas grąžinamas tik vieną kartą su fiksuotomis identiškomis reikšmėmis.

5. Dabar, kai turite užbaigtą „union“ užklausą, rodančią kiekvieno miesto pavadinimą tik vieną kartą, kartu su parinktimis, kuri iš esmės parenka visus miestus, galite naudoti šią užklausą kaip formos pasirinktinio įvedimo lauko įrašų šaltinį. Naudodami šį konkretų pavyzdį kaip modelį, galite sukurti formos pasirinktinio įvedimo lauko valdiklį, nustatyti šią užklausą kaip jos įrašų šaltinį, nustatyti stulpelio Filtras ypatybę Column Width (stulpelio plotis) kaip 0 (nulis), kad jis būtų paslėptas, ir nustatyti ypatybę Bound Column (susietas stulpelis) kaip 1, kad nurodytumėte antro stulpelio indeksą. Pačios formos ypatybėje Filter (filtras) galite įtraukti kodą, tokį kaip nurodytas toliau, kad suaktyvintumėte formos filtrą naudodami to, kas buvo pasirinkta pasirinktinio įvedimo lauko valdiklyje, reikšmę.

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

   Tada formos naudotojas gali filtruoti formos įrašus pagal konkretų miesto pavadinimą arba pasirinkti <All>, kad būtų pateikiami visų miestų įrašai.

Puslapio viršus