Mitme päringu ühendamine ühe tulemuse saamiseks ühispäringu abil

Mitme päringu ühendamine ühe tulemuse saamiseks ühispäringu abil

Vahel võib teil tekkida soov loetleda ühe tabeli või päringu kirjed koos mõne muu tabeli kirjetega, et moodustada neist ühe kirjekomplekti ehk loendi kõigi kahes või enamas tabelis leiduvate kirjetega. Accessis aitab seda teha ühispäring.

Ühispäringute mõistmiseks peaksite esmalt olema tuttav lihtsate valikupäringute koostamisega Accessis. Valikupäringute koostamise kohta leiate lisateavet artiklist Lihtsa valikupäringu loomine.

Märkus.: Selle artikli sisu on mõeldud kasutamiseks Accessi töölauaandmebaasides. Accessi veebiandmebaasides ega Accessi veebirakendustes ei saa ühispäringut luua ega kasutada.

Toimiva ühispäringuga tutvumine

Kui te pole varem ühispäringuid loonud, võiksite esmalt tutvuda töötava ühispäringu näitega Northwindi Accessi mallis. Northwindi näidismalli saate otsida Accessi alustuslehelt. Selleks valige Fail > Uus. Samuti saate koopia otse sellest asukohast alla laadida: Northwindi näidismall.

Pärast seda, kui Access on Northwindi andmebaasi avanud, sulgege alustuseks kuvatav sisselogimisdialoog ja seejärel laiendage navigeerimispaan. Klõpsake navigeerimispaani ülaosas ja valige siis Objekti tüüp, et korraldada kõik andmebaasiobjektid tüübi alusel. Seejärel laiendage jaotist Päringud. Peaksite nägema päringut nimega Product Transactions (Tootetehingud).

Ühispäringuid on lihtne teistest päringuobjektidest eristada, kuna need on märgitud kahe hulga ühisosa tähistava ikooniga, mis meenutab kahte omavahel ühendatud ringi:

Accessi ikooni Ühispäring kuvatõmmis.

Erinevalt tavalistest valiku- ja toimingupäringutest pole tabelid ühispäringus omavahel seotud. See tähendab, et Accessi graafilist päringukoosturit ei saa ühispäringute loomiseks ega redigeerimiseks kasutada. Märkate seda kindlasti kohe, kui avate ühispäringu navigeerimispaani kaudu: Access avab päringu ja kuvab tulemid andmelehevaates. Menüü Avaleht jaotises Vaated näete, et Kujundusvaade pole ühispäringutega töötamisel saadaval. Ühispäringutega töötamisel on valikute hulgas üksnes Andmelehevaade ja SQL-i vaade.

Ühispäringu näitega edasi tutvumiseks valige päringu määratleva SQL-i süntaksi vaatamiseks Avaleht > Vaated > SQL-i vaade. Selles illustratsioonis oleme SQL-i lisanud tühikuid, et teil oleks lihtsam aru saada erinevatest osadest, millest ühispäring koosneb.

Teie brauser ei toeta videot. Installige Microsoft Silverlight, Adobe Flash Player või Internet Explorer 9.

Vaatame selle Northwindi andmebaasi ühispäringu SQL-i süntaksit lähemalt.

SELECT [Product ID], [Order Date], [Company Name], [Transaction], [Quantity]
FROM [Product Orders]

UNION

SELECT [Product ID], [Creation Date], [Company Name], [Transaction], [Quantity]
FROM [Product Purchases]

ORDER BY [Order Date] DESC;

Selle SQL-lause esimene ja kolmas osa on sisuliselt kaks valikupäringut. Need päringud toovad kaks erinevat kirjekomplekt, ühe tabelist Product Orders (Tootetellimused) ja teise tabelist Product Purchases (Tooteostud).

Selle SQL-lause teine osa on võtmesõna UNION, mis ütleb Accessile, et see päring ühendab need kaks kirjekomplekti.

SQL-lause viimane osa määrab ära kombineeritud kirjete järjestuse, kasutades selleks lauset ORDER BY. Selles näites järjestab Access kõik kirjed tellimiskuupäeva välja „Order Date“ järgi laskuvas järjestuses.

Märkus.: Ühispäringud on Accessis alati kirjutuskaitstud: andmelehevaates ei saa te väärtusi muuta.

Ühispäringu loomine valikupäringute loomise ja kombineerimise kaudu

Ehkki ühispäringu saab luua ka SQL-i süntaksi otse SQL-i vaates kirja pannes, võite päringu ka valikupäringute abil osadena koostada, kuna see võib osutuda lihtsamaks. Seejärel saate SQL-i osad kopeerida ja kombineeritud ühispäringuks kleepida.

Kui soovite juhiste lugemise vahele jätta ja selle asemel näidet vaadata, lugege järgmist jaotist Ühispäringu koostamise näite vaatamine.

  1. Klõpsake menüü Loo jaotises Päringud nuppu Päringukujundus.

  2. Topeltklõpsake dialoogiboksis Tabeli kuvamine tabelit, mis sisaldab välju, mida soovite kaasata. Tabel lisatakse päringu kujundusaknasse.

  3. Sulgege dialoogiboks Tabeli kuvamine.

  4. Topeltklõpsake päringu kujundusaknas igat välja, mille soovite kaasata. Väljade valimisel veenduge, et lisate iga valikupäringu jaoks sama arvu välju samas järjekorras. Pöörake tähelepanu väljade andmetüüpidele ja veenduge, et need oleksid teiste liidetavate päringute väljade järjekorras samal kohal olevate väljade andmetüüpidega ühilduvad. Näiteks kui teie esimeses valikupäringus on viis välja, millest esimene sisaldab kuupäeva-/kellaajaandmeid, veenduge, et kõigis muudes koostatavates valikupäringutes oleks samuti viis välja, millest esimene sisaldab kuupäeva-/kellaajaandmeid jne.

  5. Väljadele kriteeriumide lisamiseks saate ka tippida vastavad avaldised väljaruudustiku reale Kriteeriumid.

  6. Pärast väljade ja nende kriteeriumite lisamist peaksite päringu käivitama ja selle väljundi üle vaatama. Klõpsake menüü Kujundus jaotises Tulemid nuppu Käivita.

  7. Lülitage päring kujundusvaatesse.

  8. Salvestage valikupäring, kuid ärge seda sulgege.

  9. Korrake seda protseduuri iga valikupäringu suhtes, mida soovite liita.

Nüüd, kui olete valikupäringud loonud, on aeg need omavahel kombineerida. Selles etapis loote ühispäringu SQL-lausete kopeerimise ja kleepimise abil.

  1. Klõpsake menüü Loo jaotises Päringud nuppu Päringukujundus.

  2. Sulgege dialoogiboks Tabeli kuvamine.

  3. Klõpsake menüü Kujundus jaotises Päring nuppu Ühispäring. Access peidab päringu kujundusakna ja kuvab SQL-i vaate objektivahekaardi. Praegu on SQL-i vaate objektivahekaart tühi.

  4. Klõpsake esimese valikupäringu vahekaarti, mida soovite ühispäringusse liita.

  5. Klõpsake menüü Avaleht nuppu Vaade ning seejärel käsku SQL-i vaade.

  6. Kopeerige valikupäringu SQL-lause. Klõpsake varem looma hakatud ühispäringu vahekaarti.

  7. Kleepige valikupäringu SQL-lause ühispäringu SQL-i vaate objekti vahekaardile.

  8. Kustutage SQL-päringu lõpus olev semikoolon (;).

  9. Vajutage kursori järgmisele reale viimiseks sisestusklahvi (Enter) ja tippige uuele reale UNION.

  10. Klõpsake järgmise valikupäringu vahekaarti, mida soovite ühispäringusse liita.

  11. Korrake juhiseid 5–10, kuni olete kopeerinud ja kleepinud ühispäringu SQL-i vaatesse kõikide valikupäringute SQL-laused. Ärge kustutage semikoolonit viimase valikupäringu viimase SQL-lause tagant, samuti ärge tippige midagi selle järele.

  12. Klõpsake menüü Kujundus jaotises Tulemid nuppu Käivita.

Teie ühispäringu tulemused kuvatakse andmelehevaates

Ühispäringu koostamise näite vaatamine

Siin on näide, mille saate Northwindi näidisandmebaasis uuesti luua. Ühispäring kogub inimeste nimed tabelist Customers (Kliendid) ja kombineerib need inimeste nimedega tabelist Suppliers (Hankijad). Kui soovite kaasa mõelda, täitke oma Northwindi näidisandmebaasi eksemplaris järjest siin toodud juhised.

Teie brauser ei toeta videot. Installige Microsoft Silverlight, Adobe Flash Player või Internet Explorer 9.

Selle näite koostamiseks tuleb teha järgmist.

  1. Looge valikupäringud Päring1 ja Päring2, kasutades andmeallikana vastavalt tabeleid „Customers“ ja „Suppliers“. Kuvatavate väärtustena kasutage välju First Name (Eesnimi) ja Last Name (Perekonnanimi).

  2. Looge uus päring nimega Päring3, millel pole esialgu andmeallikat. Seejärel klõpsake menüüs Kujundus nuppu Ühispäring, et muuta see päring ühispäringuks.

  3. Kopeerige ja kleepige Päring1 ja Päring2 SQL-laused aknasse Päring3. Eemaldage kindlasti üleliigne semikoolon ja lisage võtmesõna UNION. Seejärel saate tulemeid vaadata andmelehevaates.

  4. Lisage ühte päringusse järjestusklausel ja kleepige siis lause ORDER BY ühispäringu SQL-i vaatesse. Võtke arvesse, et ühispäringus (Päring3) eemaldatakse järjestuse lisamisel esmalt semikoolonid ja seejärel tabeli nimi väljanimedest.

  5. Lõplik SQL, mis kombineerib ja sordib selles ühispäringunäites nimed, on järgmine:

    SELECT Customers.Company, Customers.[Last Name], Customers.[First Name]
    FROM Customers
    
    UNION
    
    SELECT Suppliers.Company, Suppliers.[Last Name], Suppliers.[First Name]
    FROM Suppliers
    
    ORDER BY [Last Name], [First Name];

Kui tunnete end SQL-i süntaksi kirjutamisel väga tugevalt, saate ühispäringu SQL-lause loomulikult ise otse SQL-i vaates kirjutada. Siiski võib teiste päringuobjektide SQL-i kopeerimine ja kleepimine osutuda mugavamaks. Iga päring võib olla palju keerukam kui siin kasutatud lihtsate valikupäringute näited. Seetõttu võiksite iga päringut enne nende ühispäringuks liitmist põhjalikult katsetada. Kui ühispäring ei tööta, saate iga päringut eraldi korrigeerida seni, kuni see töötab, ja siis ühispäringu õige süntaksiga uuesti koostada.

Ühispäringute kohta lisateabe ja näpunäidete saamiseks lugege läbi selle artikli ülejäänud jaotised.

Eelmise jaotise näidetes, kus kasutati Northwindi andmebaasi, kombineeriti ainult kahe tabeli andmed. Ühispäringus saate aga hõlpsasti kombineerida ka kolme või enama tabeli andmeid. Näiteks saaksite eelmises näites kerge vaevaga lisada päringu väljundisse ka töötajate nimed. Selleks lisage kolmas päring ja kombineerige eelmine SQL-lause täiendava UNION-võtmesõnaga, nagu siin näidatud:

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

Kui vaatate tulemeid andmelehevaates, on kõik töötajad ära toodud näidisettevõtte nimega, mis pole tõenäoliselt väga abiks. Kui soovite, et see väli näitaks, kas inimene on teie enda ettevõtte töötaja, hankija töötaja või kliendi töötaja, saate ettevõtte nime asemel kasutada fikseeritud väärtust. SQL näeks sel juhul välja umbes selline:

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

Siin näete, kuidas tulem andmelehevaates kuvatakse. Access kuvab need viis näidiskirjet:

Employment (Töösuhe)

Last Name (Perekonnanimi)

First Name (Eesnimi)

In-house (Oma töötaja)

Freehafer

Nancy

In-house (Oma töötaja)

Giussani

Laura

Supplier (Hankija)

Glasson

Stuart

Customer (Klient)

Goldschmidt

Daniel

Customer (Klient)

Gratacos Solsona

Antonio

Kuna Access loeb nimed ainult ühispäringu esimese päringu väljundiväljadelt, saab ülalpool toodud päringut veelgi rohkem lihtsustada. Siin näete, et oleme teise ja kolmanda päringu jaotistest väljundi eemaldanud:

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

Accessi ühispäringus on järjestamine lubatud ainult üks kord, kuid iga päringut saab omaette filtreerida. Võttes aluseks eelmises jaotises kirjeldatud ühispäringu, on siin näide selle kohta, kuidas oleme iga päringu WHERE-klausli lisamise abil filtreerinud.

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

Kui avate andmelehevaate, peaksite nägema sarnaseid tulemeid:

Employment (Töösuhe)

Last Name (Perekonnanimi)

First Name (Eesnimi)

Supplier (Hankija)

Andersen

Elizabeth A.

In-house (Oma töötaja)

Freehafer

Nancy

Customer (Klient)

Hasselberg

Jonas

In-house (Oma töötaja)

Hellung-Larsen

Anne

Supplier (Hankija)

Hernandez-Echevarria

Amaya

Customer (Klient)

Mortensen

Sven

Supplier (Hankija)

Sandberg

Mikael

Supplier (Hankija)

Sousa

Luis

In-house (Oma töötaja)

Thorpe

Steven

Supplier (Hankija)

Weiler

Cornelia

In-house (Oma töötaja)

Zare

Robert

Kui ühendatavad päringud on väga erinevad, võib tekkida olukord, kus väljundväljal tuleb kombineerida eri andmetüüpidega andmeid. Sel juhul tagastab ühispäring tulemid enamasti teksti andmetüübiga, kuna see andmetüüp saab hõlmata nii teksti kui ka arve.

Selle paremaks selgitamiseks kasutame Northwindi näidisandmebaas ühispäringut Product Transactions (Tootetehingud). Avage see näidisandmebaas ja seejärel avage päring „Product Transactions“ andmelehevaates. Kümme viimast kirjet peaksid olema umbes järgmised:

Product ID (Toote ID)

Order Date (Tellimiskuupäev)

Company Name (Ettevõtte nimi)

Transaction (Tehing)

Quantity (Kogus)

77

22.01.2006

Supplier B (Hankija B)

Purchase (Ost)

60

80

22.01.2006

Supplier D (Hankija D)

Purchase (Ost)

75

81

22.01.2006

Supplier A (Hankija A)

Purchase (Ost)

125

81

22.01.2006

Supplier A (Hankija A)

Purchase (Ost)

200

7

20.01.2006

Company D (Ettevõte D)

Sale (Müük)

10

51

20.01.2006

Company D (Ettevõte D)

Sale (Müük)

10

80

20.01.2006

Company D (Ettevõte D)

Sale (Müük)

10

34

15.01.2006

Company AA (Ettevõte AA)

Sale (Müük)

100

80

15.01.2006

Company AA (Ettevõte AA)

Sale (Müük)

30

Oletagem, et soovite välja Quantity (Kogus) tükeldada kaheks: Buy (Ost) ja Sell (Müük) Samuti oletagem, et soovite ilma väärtuseta välja jaoks kasutada fikseeritud nullväärtust. Selle ühispäringu SQL näeb välja selline:

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; 

Kui avate andmelehevaate, näete, et viimased kümme kirjet kuvatakse nüüd nii:

Product ID (Toote ID)

Order Date (Tellimiskuupäev)

Company Name (Ettevõtte nimi)

Transaction (Tehing)

Buy (Ost)

Sell (Müük)

74

22.01.2006

Supplier B (Hankija B)

Purchase (Ost)

20

0

77

22.01.2006

Supplier B (Hankija B)

Purchase (Ost)

60

0

80

22.01.2006

Supplier D (Hankija D)

Purchase (Ost)

75

0

81

22.01.2006

Supplier A (Hankija A)

Purchase (Ost)

125

0

81

22.01.2006

Supplier A (Hankija A)

Purchase (Ost)

200

0

7

20.01.2006

Company D (Ettevõte D)

Sale (Müük)

0

10

51

20.01.2006

Company D (Ettevõte D)

Sale (Müük)

0

10

80

20.01.2006

Company D (Ettevõte D)

Sale (Müük)

0

10

34

15.01.2006

Company AA (Ettevõte AA)

Sale (Müük)

0

100

80

15.01.2006

Company AA (Ettevõte AA)

Sale (Müük)

0

30

Mida aga teha siis, kui soovite, et nullväärtusega väljad oleksid tühjad? SQL-i muutmise kaudu saate määrata, et nulli asemel ei kuvataks midagi. Selleks tuleb lisada võtmesõna 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;

Ent nagu andmelehevaatest näha, annab see mõnevõrra ootamatu tulemuse. Veerus Buy (Ost) on nüüd kõik väljad tühjad:

Product ID (Toote ID)

Order Date (Tellimiskuupäev)

Company Name (Ettevõtte nimi)

Transaction (Tehing)

Buy (Ost)

Sell (Müük)

74

22.01.2006

Supplier B (Hankija B)

Purchase (Ost)

 

 

77

22.01.2006

Supplier B (Hankija B)

Purchase (Ost)

 

 

80

22.01.2006

Supplier D (Hankija D)

Purchase (Ost)

 

 

81

22.01.2006

Supplier A (Hankija A)

Purchase (Ost)

 

 

81

22.01.2006

Supplier A (Hankija A)

Purchase (Ost)

 

 

7

20.01.2006

Company D (Ettevõte D)

Sale (Müük)

 

10

51

20.01.2006

Company D (Ettevõte D)

Sale (Müük)

 

10

80

20.01.2006

Company D (Ettevõte D)

Sale (Müük)

 

10

34

15.01.2006

Company AA (Ettevõte AA)

Sale (Müük)

 

100

80

15.01.2006

Company AA (Ettevõte AA)

Sale (Müük)

 

30

Põhjus on selles et Access määratleb väljade andmetüübid esimese päringu põhjal. Käesolevas näites pole märksõna Null arv.

Mis siis juhtub, kui proovite lisada väljade tühja väärtuse korral tühja stringi? Selle katse SQL võib välja näha umbes järgmine:

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;

Andmelehevaates näete, et Access toob veeru Buy (Ost) väärtused, kuid teisendab need väärtused tekstiks. Seda, et tegemist on tekstväärtustega, näitab see, et need on andmelehevaates vasakjoondatud. Neid tulemeid näete seetõttu, et esimese päringu tühi string ei ole arv. Samuti märkate kindlasti, et ka veeru Sell (Müük) väärtused on teisendatud tekstiks, kuna ostukirjed sisaldavad tühja stringi.

Product ID (Toote ID)

Order Date (Tellimiskuupäev)

Company Name (Ettevõtte nimi)

Transaction (Tehing)

Buy (Ost)

Sell (Müük)

74

22.01.2006

Supplier B (Hankija B)

Purchase (Ost)

20

 

77

22.01.2006

Supplier B (Hankija B)

Purchase (Ost)

60

 

80

22.01.2006

Supplier D (Hankija D)

Purchase (Ost)

75

 

81

22.01.2006

Supplier A (Hankija A)

Purchase (Ost)

125

 

81

22.01.2006

Supplier A (Hankija A)

Purchase (Ost)

200

 

7

20.01.2006

Company D (Ettevõte D)

Sale (Müük)

 

10

51

20.01.2006

Company D (Ettevõte D)

Sale (Müük)

 

10

80

20.01.2006

Company D (Ettevõte D)

Sale (Müük)

 

10

34

15.01.2006

Company AA (Ettevõte AA)

Sale (Müük)

 

100

80

15.01.2006

Company AA (Ettevõte AA)

Sale (Müük)

 

30

Kuidas seda mõistatust lahendada?

Lahendus on panna päring eeldama, et välja väärtus on arv. Seda saab teha järgmise avaldisega:

IIf(False, 0, Null)

Kuna kontrollitav tingimus False (Väär) pole kunagi True (Tõene), tagastab avaldis alati tühiväärtuse Null, ent Access väärtustab siiski mõlemad väljundivariandid ja otsustab, kas väljund on arvuline või tühi (Null).

Selle artikli töönäites saame avaldist kasutada nii:

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;

Võtke arvesse, et teist päringut pole vaja muuta.

Kui aktiveerite andmelehevaate, näetegi nüüd soovitud tulemust:

Product ID (Toote ID)

Order Date (Tellimiskuupäev)

Company Name (Ettevõtte nimi)

Transaction (Tehing)

Buy (Ost)

Sell (Müük)

74

22.01.2006

Supplier B (Hankija B)

Purchase (Ost)

20

 

77

22.01.2006

Supplier B (Hankija B)

Purchase (Ost)

60

 

80

22.01.2006

Supplier D (Hankija D)

Purchase (Ost)

75

 

81

22.01.2006

Supplier A (Hankija A)

Purchase (Ost)

125

 

81

22.01.2006

Supplier A (Hankija A)

Purchase (Ost)

200

 

7

20.01.2006

Company D (Ettevõte D)

Sale (Müük)

 

10

51

20.01.2006

Company D (Ettevõte D)

Sale (Müük)

 

10

80

20.01.2006

Company D (Ettevõte D)

Sale (Müük)

 

10

34

15.01.2006

Company AA (Ettevõte AA)

Sale (Müük)

 

100

80

15.01.2006

Company AA (Ettevõte AA)

Sale (Müük)

 

30

Teine võimalus sama tulemuse saamiseks on lisada ühispäringu päringute ette veel üks päring:

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

Iga välja korral tagastab Access fikseeritud väärtused, millel on teie määratletud andmetüüp. Kuna te ei soovi, et selle päringu väljund hakkaks tulemusi segama, tuleks siin lisada WHERE-klausel väärtusele False (Väär):

WHERE False

Kuna see väärtus on alati väär, ei tagasta see päring midagi. Selle lause kombineerimine olemasoleva SQL-iga annabki meile tulemuseks lõpliku lause:

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;

Märkus.: Meie näite kombineeritud päring, mis kasutab Northwindi andmebaasi, tagastab 100 kirjet; kaks individuaalset päringut seevastu tagastavad 58 ja 43 kirjet ehk kokku 101 kirjet. Lahknevuse põhjustab asjaolu, et kaks kirjet pole kordumatud. Jaotisest Eristatavate kirjetega töötamine ühispäringutes, kasutades võtmesõna UNION ALL saate teada, kuidas seda stsenaariumi lahendada, kasutades võtmesõna UNION ALL.

Ühispäringus saab kombineerida kirjekomplekti ühe kirjega, mis sisaldab ühe või mitme välja summat.

Siin on järgmine näide, mille saate ise Northwindi näidisandmebaasis luua ja mis annab teile aimu sellest, kuidas ühispäringus summat leida.

  1. Looge uus lihtpäring õlleostude vaatamiseks (Northwindi andmebaasis Product ID=34), kasutades järgmist SQL-i süntaksit:

    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. Aktiveerige andmelehevaade. Peaksite nägema nelja ostu:

    Date Received (Vastu võetud)

    Quantity (Kogus)

    22.01.2006

    100

    22.01.2006

    60

    04.04.2006

    50

    05.04.2006

    300

  3. Summa leidmiseks looge lihtne kokkuvõttepäring, kasutades järgmist SQL-i:

    SELECT Max([Date Received]), Sum([Quantity]) AS SumOfQuantity
    FROM [Purchase Order Details]
    WHERE ((([Purchase Order Details].[Product ID])=34))
  4. Aktiveerige andmelehevaade. Peaksite nägema ainult ühte kirjet:

    MaxOfDate Received

    SumOfQuantity

    05.04.2006

    510

  5. Kombineerige need kaks päringut ühispäringuks, et lisada kogukogusega kirje ostukirjetele:

    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. Aktiveerige andmelehevaade. Peaksite nägema nelja ostu; iga ostu summale peaks järgnema koguse kokkuvõttega kirje:

    Date Received (Vastu võetud)

    Quantity (Kogus)

    22.01.2006

    60

    22.01.2006

    100

    04.04.2006

    50

    05.04.2006

    300

    05.04.2006

    510

Kokkuvõtete või summade lisamine ühispäringusse käibki põhimõtteliselt nii. Kui soovite kokkuvõttekirje muudest kirjetest visuaalselt eristada, võite mõlemasse päringusse kaasata fikseeritud väärtused, näiteks „Detail“ (Üksikasi) ja „Total“ (Kokkuvõte). Fikseeritud väärtuste kasutamisest leiate ülevaate jaotisest Kolme või enama tabeli või päringu kombineerimine ühispäringuks.

Vaikimisi kaasatakse Accessis ühispäringutesse ainult eristatavad kirjed. Mida aga teha siis, kui soovite kaasata kõik kirjed? Siin võib abil olla teisest näitest.

Eelmises jaotises näitasime teile, kuidas luua ühispäringus kokkuvõte. Muutke selle ühispäringu SQL-i, et kaasata 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];

Aktiveerige andmelehevaade. Peaksite nägema mõnevõrra eksitavat tulemust:

Date Received (Vastu võetud)

Quantity (Kogus)

22.01.2006

100

22.01.2006

200

Muidugi ei tagasta üks kirje kokkuvõttes kahekordset kogust.

Põhjus, miks te seda tulemust näete, on lihtne: ühel päeval müüdi sama šokolaadikogus kaks korda. See on kirjas tabelis „Purchase Order Details“ (Ostutellimuse üksikasjad). Järgmine lihtne valikupäring näitab mõlemat kirjet Northwindi näidisandmebaasis:

Purchase Order ID (Ostutellimuse ID)

Product (Toode)

Quantity (Kogus)

100

Northwind Traders Chocolate

100

92

Northwind Traders Chocolate

100

Nagu näete, ei sisalda varem loodud ühispäring välja „Purchase Order ID“ ning need kaks välja ei moodusta kahte eristatavat kirjet.

Kui soovite kaasata kõik kirjed, kasutage oma SQL-is võtmesõna UNION asemel võtmesõna UNION ALL. Kuna see mõjutab arvatavasti tulemite sortimist, võiksite sortimisjärjestuse määratlemiseks kaasa ka klausli ORDER BY. Eelmisel näitel põhinev muudetud SQL on järgmine:

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

Aktiveerige andmelehevaade. Peaksite nägema kõiki üksikasju ja lisaks viimase kirjena ka kokkuvõtet:

Date Received (Vastu võetud)

Total (Kokku)

Quantity (Kogus)

22.01.2006

 

100

22.01.2006

 

100

22.01.2006

Total (Kokku)

200

Sageli kasutatakse ühispäringuid vormil liitboksi juhtelemendi kirjeallikana. Selle liitboksi kaudu saate valida vormi kirjete filtreerimiseks soovitud väärtuse. Näiteks saate töötajakirjeid linna alusel filtreerida.

Kui soovite näha, kuidas see töötab, on siin järgmine näide, mille saate selle stsenaariumi illustreerimiseks ise Northwindi näidisandmebaasis luua.

  1. Looge lihtne valikupäring, kasutades seda SQL-i süntaksit:

    SELECT Employees.City, Employees.City AS Filter
    FROM Employees;
  2. Aktiveerige andmelehevaade. Peaksite nägema järgmist tulemust:

    City (Linn)

    Filter

    Seattle

    Seattle

    Bellevue

    Bellevue

    Redmond

    Redmond

    Kirkland

    Kirkland

    Seattle

    Seattle

    Redmond

    Redmond

    Seattle

    Seattle

    Redmond

    Redmond

    Seattle

    Seattle

  3. See pilt ei pruugi teile anda eriti väärtuslikku teavet. Päringut saate aga laiendada ja teisendada selle ühispäringuks, kasutades järgmist SQL-i:

    SELECT Employees.City, Employees.City AS Filter
    FROM Employees
    
    UNION
    
    SELECT "<All>", "*" AS Filter
    FROM Employees
    
    ORDER BY City;
  4. Aktiveerige andmelehevaade. Peaksite nägema järgmist tulemust:

    City (Linn)

    Filter

    <All>

    *

    Bellevue

    Bellevue

    Kirkland

    Kirkland

    Redmond

    Redmond

    Seattle

    Seattle

    Access liidab kõik üheksa varem kuvatud kirjet, kasutades fikseeritud väljaväärtusi <All> ja „*“.

    Kuna see ühendamisklausel ei sisalda võtmesõna UNION ALL, tagastab Access ainult eristatavad kirjed, mis tähendab, et iga linn tagastatakse fikseeritud samaste väärtuste korral ainult üks kord.

  5. Nüüd, kui teil on olemas lõpetatud ühispäring, kus iga linna nimi kuvatakse ainult üks kord ja mis sisaldab ka kõigi linnade valimise võimalust, saategi seda päringut kasutada vormi liitboksi kirjeallikana. Seda konkreetset näidet mudelina kasutades võiksite vormil luua liitboksi juhtelemendi, määrata selle päringu juhtelemendi kirjeallikaks, määrata veeru Filter atribuudi Column Width (Veeru laius) väärtuseks 0 (null), et see visuaalselt peita, ja seejärel määrata atribuudi Bound Column (Seotud veerg) väärtuseks 1, et osutada teise veeru indeksile. Vormi enda atribuudis Filter saate seejärel lisada näiteks järgmise koodi, et aktiveerida vormifilter liitboksi juhtelemendis valitud väärtuse abil:

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

    Vormi kasutaja saab seejärel vormikirjeid filtreerida, et kuvada kindel linn, või valida <All> (Kõik) kõigi linnade kirjete loetlemiseks.

Lehe algusse

Täiendage Office'i kasutamise oskusi
Tutvuge koolitusmaterjalidega
Kasutage uusi funktsioone enne teisi
Liituge Office Insideri programmiga

Kas sellest teabest oli abi?

Täname tagasiside eest!

Täname tagasiside eest! Tundub, et võiksime teid kokku viia ühega meie Office'i tugiagentidest, kes aitab teil probleemi lahendada.

×