Opis uporabe združevanj v Microsoft Queryju

Kaj je združevanje?

Združevanje je povezava med dvema tabelama, kjer se združita dve tabeli, glede na polje, ki ga imata v skupni rabi, ustvarjanje nove virtualne tabele (ki jo je mogoče shraniti kot realno tabelo). Na primer s temi dvema tabelama: Color_Table: Join_Field Color_Field 1 Red 2 Blue 3 Green Pattern_Table: Join_Field Pattern_Field 2 Striped 3 Checkered 4 Polka-Dot preprosto združevanje je podobno temu: Join_Field Color_Field Pattern_Field 2 Blue Striped 3 Green Checkered V tabeli z rezultati so prikazani le zapisi 2 in 3 v polju» Join_Field «, ker so le zapisi, ki obstajajo v Color_Table in Pattern_Table. Praktični primer združevanja je seznam izdelkov in proizvajalcev na drobno; Tabela» izdelki «in tabela» prodajalci «se lahko združita v polje z ID-jem izdelka.

Notranje združevanje

Pridruževanje v prejšnjem primeru, ki se imenuje notranje združevanje, je Najpreprostejša vrsta združevanja. Običajno želite uporabiti le delček polj v tabelah. Na primer, v realnem notranjem stiku, kot je zgoraj, boste morda želeli izključiti Join_Field, tako da je združevanje videti tako: Color_Field Pattern_Field Blue Striped Green Checkered V Microsoft Queryju so Notranja združevanja privzeta vrsta združevanja (če želite več informacij, glejte stran 105 v priročniku» Microsoft Query user ' s Guide «, različica 1,0).

Izjava SQL

Izjava» strukturirana poizvedba «(SQL) SELECT je vrsta makra, ki ga lahko uporabite pri ustvarjanju združevanja. Upoštevajte, da se SQL zelo razlikuje od drugih jezikov makra Microsoft Excela (Visual Basic for Applications in jezik makrov v Excelu 4,0). Ni treba razumeti SQL, da lahko preprosto ustvarite združevanja v Microsoft Queryju. Vsaka združitev ima izjavo SELECT, ki je povezana z njo. Izjavo SELECT lahko prikažete za poljubno združevanje v Microsoft Queryju tako, da kliknete» SQL «v orodni vrstici. Kot je Microsoft Excelov snemalnik makrov, lahko uporabite Query, da posnamete izjavo SELECT. V nasprotju z Microsoft Excelovimi snemalnikom makrov je vedno vklopljen snemalnik izjav SELECT in ga ni mogoče izklopiti. Tako je izjava SELECT morda videti v Microsoft Queryju za zgornje notranje združevanje: SELECT Color_Table.Color_Field, Pattern_Table.Pattern_Field FROM `c:\database`.Color_Table, `c:\database`.Pattern_Table WHERE Color_Table.Join_Field = Pattern_Table.Join_Field Upoštevajte, da uporabljamo ime zbirke podatkov» C:\database «, katerega. mdb je pripona imena datoteke za Microsoft Accessovo zbirko podatkov, ki ima lahko več tabel v eni datoteki. V nekaterih drugih zbirkah podatkov, kot so dBASE, Paradox in FoxPro, mora imeti vsaka tabela svojo datoteko. V teh primerih je sintaksa SQL morda odveč, ker je ime tabele vedno enako kot ime datoteke brez pripone. Sintaksa SQL se razlikuje med motorji poizvedbe; na primer, v Microsoft Accessu je poizvedba iz zgornjega primera podobna tej: SELECT Color_Table.[Color_Field], Pattern_Table.Pattern_Field FROM Pattern_Table INNER JOIN Color_Table ON Pattern_Table.[Join_Field] = Color_Table.[Join_Field]; Pot do tabele se ne uporablja v Microsoft Accessu, ker je tabela v datoteki Microsoft Access. mdb. Tudi če je zunanja tabela priključena in uporabljena v poizvedbi, Microsoft Access SQL izjava ne prikaže poti do zunanje tabele.

Zunanje združevanje

Druga vrsta združevanja se imenuje zunanje združevanje. Z zunanjim združevanjem dobite vse zapise iz ene tabele in le tiste zapise iz druge tabele, ki imajo ujemajoče se vrednosti iz prve tabele. To lahko pusti nekaj vnosov polja prazno ali» NULL «. Če želite združiti dve tabeli, sta na voljo dve možni zunanji združitvi,» leva zunanja pridružitev «in» desna zunanja pridružitev «(tako imenovana, ker si po navadi ogledujete tabele vzporedno). S prejšnjimi dvema tabelama v primeru je to eden od dveh možnih zunanjih stikov: Join_Field Color_Field Pattern_Field 1 Red (NULL) 2 Blue Striped 3 Green Checkered Druga možna združitev je takšna: Join_Field Color_Field Pattern_Field 2 Blue Striped 3 Green Checkered 4 (NULL) Polka-Dot Opomba: ko si ogledujete podatke, se v pridruževanju ne prikaže beseda» NULL «. Ko delate s stiki, uporabite ključno besedo» NULL «. V Microsoft Queryju lahko obe vrsti zunanjega združevanja preprosto ustvarite z miško (če želite več informacij o tem postopku, glejte stran 112» priročnika za Microsoft Query «, različica 1,0). V nadaljevanju je prikazano, kako bo izjava SQL morda poiskala drugi primer zunanjega združevanja: SELECT Color_Table.Color_Field, Pattern_Table.Pattern_Field FROM {oj `c:\database`.Color_Table LEFT OUTER JOIN `c:\database`.Pattern_Table ON Color_Table.Join_Field = Pattern_Table.Join_Field} Če želite ustvariti praktičen primer zunanjega združevanja, naredite seznam izdelkov družbe s številkami prodaje za izdelke, ki so bili prodani, ne da bi izključili izdelke, ki niso bili prodani. Če želite to narediti, uporabite polje ID izdelka, da se pridružite tabeli» izdelki «in tabeli» Prodaja «.

Odštevanje združevanja

Tretja vrsta združevanja je odštevanje združevanja. Odštevanje združevanja je nasprotno od zunanjega združevanja; vključuje le zapise v eni tabeli, ki se ne ujemajo z nobenim zapisom v drugi tabeli. Tako kot zunanja združevanja obstajata dve možni odšteti združevanja za katero koli dve tabeli, ki se ji želite pridružiti; vendar pa se običajno ne imenujejo» Left odštevanje JOIN «ali» desno odštevanje JOIN «. Odštevanje združevanja, ki se običajno vrne iz ene od tabel, ker po definiciji polja druge tabele vrnejo le NIČELNe vrednosti. To je ena od možnih odštevanja združevanja: join_Field Color_Field 1 Red in tukaj je druga: Join_Field Pattern_Field 4 Polka-Dot V Microsoft Queryju ustvari odštevanje združevanja tako, da najprej ustvarite zunanjo združevanje in nato uporabite pogoje» je NULL «v ustreznem polju (Pattern_Field v prvem zgornjem primeru; Color_Field v drugem primeru), da izločite zapise, ki se ujemajo med tabelami. V nadaljevanju je navedeno, kako bo izjava SQL morda poiskala prvo odštevanje, ki se bo prikazalo zgoraj: SELECT Color_Table.Join_Field, Color_Table.Color_Field FROM {oj `c:\database`.Color_Table LEFT OUTER JOIN `c:\database`.Pattern_Table ON Color_Table.Join_Field = Pattern_Table.Join_Field} WHERE (Pattern_Table.Pattern_Field Is Null) Če želite ustvariti praktičen primer odštevanja združevanja, seznam strank, ki jih pred kratkim niste naročili. Če želite to narediti, uporabite polje» ID naročila «, da se pridružite tabeli» stranke «in tabeli» naročila «.

Celotno zunanjo združevanje

Četrta vrsta združevanja je polno zunanjo združevanje. Popolno zunanje združevanje je kombinacija zunanjega združevanja s laskavim pridruževanjem. Celotno zunanje združevanje vključuje vse zapise iz obeh tabel in spoji te zapise, ki so pogosti med obema tabelama. To je popolno zunanje združevanje: Join_Field Color_Field Pattern_Field 1 Red (NULL) 2 Blue Striped 3 Green Checkered 4 (NULL) Polka-Dot V Microsoft Queryju je ustvarjeno polno zunanjo združevanje tako, da vstavite operator SINDIKATa v svojo vrstico med izjavo SELECT (zunanji stik) in izjavo SELECT (glejte zgoraj). Če želite ustvariti poln zunanji stik v Microsoft Queryju, uporabite te korake za ustvarjanje ustreznega stavka SQL:

1. Ustvarite zunanjo združevanje in nato v meniju» Datoteka «kliknite» novo «in ustvarite odštevanje združevanja.

2. Kopirajte SQL Serverjevo pridruževanje.

3. Preklopite na zunanjo združevanje, v svojo vrstico pod izjavo SQL izjave zunanjega združevanja vnesite besedo UNION, prilepite SQL, ki je v stiku s pridruževanjem, in zaprite okno odštevanje pridruževanja.

Opomba Rezultati te tabele pogrešajo vrednost Join_Field» 4 «, ker je zapis, ki Join_Field vrednost je enak 4, v tabeli Pattern_Table. V spodnjih stavkih SQL dones't izberite polje Pattern_Table. John_Field. Join_Field Color_Field Pattern_Field (NULL) Polka-Dot 1 Red (NULL) 2 Blue Striped 3 Green Checkered Tukaj je prikazan način, kako lahko SQL poišče nad celotno zunanjo združevanje (ukazi pod operatorjem UNION je prilepljen): SELECT Color_Table.Join_Field, Color_Table.Color_Field, Pattern_Table.Pattern_Field FROM {oj `C:\database`.Pattern_Table LEFT OUTER JOIN `C:\database`.Color_Table ON Color_Table.Join_Field = Pattern_Table.Join_Field} UNION SELECT Color_Table.Join_Field, Color_Table.Color_Field, Pattern_Table.Pattern_Field FROM {oj `C:\database`.Color_Table LEFT OUTER JOIN `C:\database`.Pattern_Table ON Color_Table.Join_Field = Pattern_Table.Join_Field} WHERE (Color_Table.Color_Field Is Null) or(Pattern_Table.Pattern_Field Is Null) Če želite ustvariti praktičen primer polnega zunanjega združevanja, spojite prekrivajoče se sezname strank, ki jih uporabljajo različni oddelki, vključno s številkami faksa (ki so bile le na prvem seznamu), in internetna e-poštna imena (ki so le na drugem seznamu). Vsak oddelek lahko še naprej uporablja svoj delni seznam, medtem ko ima na voljo popoln seznam, ki je povezan. Lahko se pridružijo v polju ID stranke.

Self-JOIN

Peta vrsta združevanja je self-JOIN. Self-JOIN je povezave, kjer se polje v tabeli ujema z drugim poljem v kopiji iste tabele. Uporabite to vzorčno tabelo: Table_Three Employee_ID Employee_Name Reports_To 1 Bob 3 2 Sue (NULL) 3 Jim 2 4 Jane 3 in kopijo, kot sledi: Table_Three_01 Employee_ID Employee_Name Reports_To 1 Bob 3 2 Sue (NULL) 3 Jim 2 4 Jane 3 Če želite ustvariti seznam imen zaposlenih z imeni nadrejenih, lahko uporabite samozdruževanje. Employee_ID v Table_Three bi se pridružili Reports_To v Table_Three_01. V nadaljevanju si lahko ogledate, kako je videti na prvem mestu: Employee_Name Employee_Name Bob Jim Sue (NULL) Jim Sue Jane Jim Ker pa je zavajajoče, da ima enako ime polja za obe polji, spremenite eno od imen polj, in sicer tako: Employee_Name Supervisor Bob Jim Sue (NULL) Jim Sue Jane Jim V nadaljevanju je navedeno, kako bi lahko SQL iskal zgornji samostojni stik: SELECT table_three.Employee_Name, table_three_01.Employee_Name 'Supervisor' FROM `c:\database`.table_three, `c:\database`.table_three_01 WHERE table_three.Employee_ID = table_three_01.Reports_To Ko podatke vrnete v Microsoft Excel, to polje ni v uporabi za preimenovanje polja v Microsoft Queryju. To velja, ker Microsoft Excel uporablja izvirno ime polja. Če želite več informacij o tej težavi, si oglejte ta članek v Microsoftovi zbirki znanja: 121551 : XL5: polje namesto imena stolpca v MSQUERY, ki se vrne v Excel Microsoft Excelov makro mora spremeniti ime stolpca vsakič, ko so vrnjeni podatki osveženi (razen če vrnete podatke v vrtilni tabeli, v tem primeru lahko sam pivot ustvari in ohrani ime polja po meri).

Equi – združevanje in naravno združevanje

Skoraj vse združitve, vključno z vsemi primeri, ki so bili doslej navedeni, so equi-joins in Natural joins. Pomen teh izrazov je za povprečnega uporabnika Microsoft Querya zelo pomemben, toda naslednji dve točki poskušata pojasniti pogoje za osebe, ki so morda radovedne. Equi – JOIN je pridruževanje, v katerem so zapisi pridobljeni na podlagi tega, ali imajo polja združevanja ujemajoče se vrednosti v obeh tabelah. To se morda zdi le definicija združevanja, vendar ni. Primer neenakega združevanja je pridružitev, kjer so zapisi v prvi tabeli združeni v te zapise v drugi tabeli, v kateri je polje» združeno «v prvi tabeli večje kot (namesto enako) polje» združeno «v drugi tabeli (ali manj kot enako). To seveda vrne več zapisov, kot je equi. Naravna združitev je tista, v kateri je vrnjena le ena od polj, ki so povezana z dvema tabelama. Ker sta ti dve polji po definiciji enaki v equi-JOIN, je odveč, če želite vključiti oboje. Za združevanje, ki ni enako, je pomembno, da vključite obe polji. Torej, equi-joins in Natural joins go Together. Želeli bi equi (ki opisuje večino združevanj) za naravno združevanje, tako da vrne le eno od povezanih polj; Če pa ne želite uporabiti neenakega združevanja, boste morda želeli ustvariti nenaravno združevanje tako, da boste vrnili obe polji v stiku. Obstajajo tudi druge vrste združevanja. Celoten spekter združevanj je bil nedavno določen v programu 1992, ta standard pa je poznan kot SQL-92. Nekatera združevanja niso pomembna za uporabnike Microsoft Excela, ker te združitve naredijo stvari, ki jih je v Microsoft Excelu lažje opraviti.

Izdelek kartezijski

Poskušate vrniti podatke iz dveh ali več tabel brez združevanja ustvari to, kar se imenuje» kartezijski izdelek «. Izdelek kartezijski je opredeljen kot vse možne kombinacije vrstic v vseh tabelah. Prepričajte se, da imate stike, preden poskušate vrniti podatke, ker je kartezijski izdelek v tabelah s številnimi zapisi in/ali številnimi tabelami lahko trajal več ur. V nadaljevanju je kartezijski izdelek, kot je uporabljen v dveh vzorčnih tabelah; Upoštevajte, da je ta tabela le 3 zapisi Times 3 Records, ki dopušča skupno 9 zapisov. Vendar pa si predstavljajte, če je bila tabela 100 Records Times 1.000 Records Times 10.000 Records; nato tabela vsebuje 1.000.000.000 zapisov! Join_Field Join_Field Color_Field Pattern_Field 1 2 Red Striped 1 3 Red Checkered 1 4 Red Polka-Dot 2 2 Blue Striped 2 3 Blue Checkered 2 4 Blue Polka-Dot 3 2 Green Striped 3 3 Green Checkered 3 4 Green Polka-Dot Občasno nekateri uporabniki želijo uporabljati izdelek kartezijski; vendar pa večina uporabnikov, ki jih dobijo po nesreči, pogosto zamenjuje z njimi. Ker večina uporabnikov izključi večino polj v stiku, lahko dejanski kartezijski izdelek preprosto izgleda tako, kot je zapleteno: Color_Field Red Red Red Blue Blue Blue Green Green Green Če so 100 zapisi dodani v Pattern_Table, bi ta poizvedba imela 309 zapisov (103 Records vsak od rdečih, modrih in zelenih). Kartezijski izdelki imajo razmeroma preproste izjave SELECT. V nadaljevanju je navedeno, kako lahko SQL išče zgornji kartezijski izdelek: SELECT Color_Table.Color_Field, Pattern_Table.Pattern_Field FROM `c:\database`.Color_Table, `c:\database`.Pattern_Table Praktični primer izdelka kartezijski bi bil ustvariti seznam vseh možnih kombinacij možnosti na izdelku blaga, s skupnimi cenami za vsako kombinacijo.