Tämä on toinen alakyselyjä käsittelevässä artikkelisarjassa. Tässä artikkelissa käsitellään SELECT-lauseen sarakeluettelon alakyselyjä. Muut artikkelit käsittelevät niiden käyttöä muissa lausekkeissa.
Kaikki tämän oppitunnin esimerkit perustuvat Microsoft SQL Server Management Studioon ja AdventureWorks2012-tietokantaan. Voit aloittaa näiden ilmaisten työkalujen käytön Oppaalla Aloitusopas SQL Serverin avulla.
Alakyselyjen käyttäminen Valitse lauseessa
Kun alikysely sijoitetaan sen sarakeluetteloon, johon se on tottunut palauttaa yksittäiset arvot. Tässä tapauksessa voit ajatella alakyselyä yhtenä arvolausekkeena. Palautettu tulos ei ole erilainen kuin ilmaisu ”2 + 2.” Tietysti myös alakyselyt voivat palauttaa tekstiä, mutta ymmärrät asian!
Kun työskentelet alikyselyjen kanssa, päälauseketta kutsutaan joskus ulommaksi kyselyksi. Alikyselyt ovat sulkeissa, mikä helpottaa niiden havaitsemista. .
Ole varovainen, kun käytät alakyselyjä. Niitä voi olla hauska käyttää, mutta kun lisäät kyselyyn, he voivat alkaa hidastaa kyselyäsi.
Yksinkertainen kysely keskiarvon laskemiseksi.
Aloitetaan yksinkertaisella kyselyllä, jolla näytetään SalesOrderDetail ja verrataan sitä yleiseen keskimääräiseen SalesOrderDetail LineTotal -arvoon. Käytettävä SELECT-käsky on:
SELECT SalesOrderID,LineTotal,(SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail) AS AverageLineTotalFROM Sales.SalesOrderDetail;
Tämä kysely palauttaa tulokset muodossa:
Yläpuolella punaisella näkyvä alakysely suoritetaan ensin keskimääräisen LineTotal-arvon saamiseksi.
SELECT AVG(LineTotal)FROM Sales.SalesOrderDetail
Tämä tulos kytketään sitten takaisin sarakeluetteloon ja kysely jatkuu. Haluan tuoda esiin useita asioita :
- Alakyselyt ovat sulkeissa .
- Kun alakyselyjä käytetään SELECT-käskyssä, ne voivat palauttaa vain yhden arvon. Tämän pitäisi olla järkevää, yksinkertaisesti sarakkeen valitseminen palauttaa yhden arvon riville, ja meidän on noudatettava samaa mallia.
- Alakysely suoritetaan yleensä vain kerran koko kyselylle, ja sen tulosta käytetään uudelleen . Tämä johtuu siitä, että kyselyn tulos ei vaihdella jokaisella palautetulla rivillä.
- Luettavuuden parantamiseksi on tärkeää käyttää aliaksia sarakkeiden nimissä.
Yksinkertainen kysely lausekkeessa
Kuten saatat odottaa, alikyselyn tulosta voidaan käyttää muissa lausekkeissa. Edellisen esimerkin pohjalta määritetään alikyselyllä, kuinka paljon LineTotal vaihtelee keskiarvosta.
Varianssi on yksinkertaisesti LineTotal miinus keskimääräinen viiva yhteensä. Seuraavassa alakyselyssä olen värittänyt sen siniseksi. Tässä on varianssin kaava:
LineTotal - (SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail)
Sulkeisiin suljettu SELECT-käsky on alakysely. Kuten edellisessä esimerkissä, tämä kysely suoritetaan kerran, palauttaa numeerisen arvon, joka vähennetään sitten jokaisesta LineTotal-arvosta.
Tässä on kysely lopullisessa muodossa:
SELECT SalesOrderID, LineTotal, (SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail) AS AverageLineTotal, LineTotal - (SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail) AS VarianceFROM Sales.SalesOrderDetail
Tässä on tulos:
Kun työskentelen alakyselyjen kanssa tietyissä lauseissa, yleensä rakennan ja testaa alikysely ensin. SELECT-lauseet voivat monimutkaistua hyvin nopeasti. On parasta rakentaa ne vähitellen. Rakentamalla ja testaamalla eri kappaleet erikseen, se auttaa todella virheenkorjauksessa.
Vastaavat kyselyt
On olemassa tapoja sisällyttää ulkoisen kyselyn arvot alikyselyn lausekkeisiin. Tämän tyyppisiä kyselyitä kutsutaan korreloiduiksi alakyselyiksi, koska alikyselyn tulokset ovat jossain muodossa yhteydessä ulomman kyselyn arvoihin. Vastaavia kyselyitä kutsutaan joskus synkronoiduiksi kyselyiksi.
Jos sinulla on vaikeuksia tietää, mitä korrelaatti tarkoittaa, tutustu tähän Googlen määritelmään:
Correlate: ”sinulla on keskinäinen suhde tai yhteys, jossa yksi asia vaikuttaa tai riippuu toisesta. ”
Tyypillistä vastaavaan kyselyyn liittyvää käyttöä käytetään sisäisen kyselyn WHERE-lausekkeen jossakin ulomman kyselyn sarakkeessa. Tämä on järkeä monissa tapauksissa, jos haluat rajoita sisempi kysely tietojen alajoukkoon.
Korreloitu alikyselyesimerkki
Tarjoamme vastaavan alikyselyesimerkin raportoimalla kaikki SalesOrderDetail LineTotal ja keskimääräiset LineTotal kokonaismyynnit Tilaus.
Tämä pyyntö eroaa huomattavasti aikaisemmista esimerkkeistämme, koska laskemamme keskiarvo vaihtelee kullekin myyntitilaukselle.
Täällä esiintyy vastaavia alakyselyjä. Voimme käyttää arvo ulkoisesta kyselystä ja sisällytä se alakyselyn suodatuskriteereihin.
Otetaan a katso kuinka laskemme keskimääräisen viivan kokonaismäärän. Tätä varten olen koonnut kuvan, joka näyttää SELECT-käskyn alikyselyllä.
Tarkemmin kaavio. SELECT-käsky koostuu kahdesta osasta, ulommasta kyselystä ja alakyselystä. Ulkoista kyselyä käytetään kaikkien SalesOrderDetail-rivien noutamiseen.Alakyselyä käytetään etsimään ja tiivistämään myyntitilausten tietorivit tietylle SalesOrderID-tunnukselle.
Jos aioin sanata vaiheet Otamme yhteenvedon niistä seuraavasti:
- Hanki SalesOrderID.
- Palauta keskiarvo LineTotal kaikista SalesOrderDetail-kohteista, joissa SalesOrderID vastaa.
- Jatka ulkoisen kyselyn seuraavaan SalesOrderID-tunnukseen ja toista vaiheet 1 ja 2.
AdventureWork2012-tietokannassa suoritettava kysely on:
SELECT SalesOrderID, SalesOrderDetailID, LineTotal, (SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail WHERE SalesOrderID = SOD.SalesOrderID) AS AverageLineTotalFROM Sales.SalesOrderDetail SOD
Tässä ovat kyselyn tulokset:
On olemassa pari kohteet.
- Näet, että käytin sarakkeiden aliaksia helpottaakseni kyselytulosten lukemista.
- Käytin myös taulukon aliasta, SOD, ulompi kysely. Tämä antaa mahdollisuuden käyttää ulkoisen kyselyn arvoja alakyselyssä. Muuten kysely ei ole korreloitu!
- Taulukoiden aliasten käyttäminen tekee yksiselitteisistä, mitkä sarakkeet ovat kustakin taulukosta.
Vastaavan alikyselyn erittely
Yritetään nyt eritellä tämä SQL: n avulla.
Aluksi oletetaan, että haemme vain esimerkki SalesOrderDetailID 20: stä. Vastaava SalesOrderID on 43661.
Tämän kohteen keskimääräisen rivisumman saaminen on helppoa
SELECT AVG(LineTotal)FROM Sales.SalesOrderDetailWHERE SalesOrderID = 43661
Tämä palauttaa arvon 2181,765240.
Nyt kun meillä on keskiarvo, voimme liitä se kyselymme. :
SELECT SalesOrderID, SalesOrderDetailID, LineTotal, (SELECT AVG(LineTotal) FROM Sales.SalesOrderDetailWHERE SalesOrderID = SOD.SalesOrderID) AS AverageLineTotalFROM Sales.SalesOrderDetail AS SOD
Korreloitu alikysely eri taulukolla
Korreloitu alikysely tai mitä tahansa alikysely voi käyttää eri taulukkoa ulompi kysely. Tämä voi olla hyödyllistä, kun työskentelet ”vanhemman” taulukon, kuten SalesOrderHeader, kanssa ja haluat sisällyttää tulokseen yhteenvedon aliriveistä, kuten SalesOrderDetail.
Palautetaan OrderDate, TotalDue ja myyntitilausten tietorivien määrä. Voit tehdä tämän käyttämällä seuraavaa kaaviota saadaksemme suuntaviivat:
Tätä varten sisällytämme korreloidun alakyselyn SELECT-lauseeseemme palauttamaan SalesOrderDetail-rivien COUNT. Varmistamme, että laskemme oikean SalesOrderDetail-kohteen suodattamalla ulkoisen kyselyn SalesOrderID-tunnuksen.
Tässä on viimeinen SELECT-lause:
SELECT SalesOrderID, OrderDate, TotalDue, (SELECT COUNT(SalesOrderDetailID) FROM Sales.SalesOrderDetail WHERE SalesOrderID = SO.SalesOrderID) as LineCountFROM Sales.SalesOrderHeader SO
Tulokset ovat:
Tässä esimerkissä huomioitavia asioita ovat:
- Alakysely valitsee tietoja toisesta taulukosta kuin ulompi kysely.
- Käytin taulukkoa ja sarake-aliakset helpottamaan SQL: n ja tulosten lukemista.
- Muista tarkistaa kaksinkertaisesti k sinun lauseke! Jos unohdat sisällyttää taulukon nimen tai aliaksia alakyselyn WHERE-lausekkeeseen, kyselyä ei korreloida.
Korreloivat alikyselyt ja sisäiset liitokset
On tärkeää ymmärtääksesi, että voit saada samat tulokset käyttämällä joko alikyselyä tai liittymistä. Vaikka molemmat tuottavat samat tulokset, jokaisella menetelmällä on etuja ja haittoja!
Tarkastellaan viimeistä esimerkkiä, jossa laskemme rivikohtia SalesHeader-tuotteille.
SELECT SalesOrderID, OrderDate, TotalDue, (SELECT COUNT(SalesOrderDetailID) FROM Sales.SalesOrderDetailWHERE SalesOrderID = SO.SalesOrderID) as LineCountFROM Sales.SalesOrderHeader SO
Tämä sama kysely voidaan tehdä käyttämällä SISÄISEN LIITTYMISTÄ yhdessä GROUP BY: n kanssa nimellä
SELECT SO.SalesOrderID, OrderDate, TotalDue, COUNT(SOD.SalesOrderDetailID) as LineCountFROM Sales.SalesOrderHeader SO INNER JOIN Sales.SalesOrderDetail SOD ON SOD.SalesOrderID = SO.SalesOrderIDGROUP BY SO.SalesOrderID, OrderDate, TotalDue
Mikä on nopeampi?
Huomaat, että monet ihmiset sanovat välttävän alakyselyjä, koska ne ovat hitaampia. He väittävät, että vastaavan alikyselyn on ”suoritettava” kerran kutakin ulommassa kyselyssä palautettua riviä kohden, kun taas INNER JOINin on suoritettava vain yksi tietojen kulku.
Itse? Sanon check out noudin omia neuvojani molempien yllä olevien esimerkkien suhteen ja huomasin suunnitelmien olevan samat!
Tämä ei tarkoita sitä, että suunnitelmat muuttuisivat, jos tietoja olisi enemmän, mutta mielipiteeni on, että sinun ei pitäisi tehdä vain oletuksia. Useimmat SQL DBMS -optimoijat voivat todella selvittää parhaan tavan suorittaa kyselysi. Ne ottavat syntaksisi, kuten alikyselyn tai INNER JOIN, ja käyttävät niitä luomaan varsinainen toteutussuunnitelma.
Kumpi on helpommin luettavissa?
Riippuen siitä, mihin olet tyytyväinen, INNER JOIN -esimerkki voi olla helpommin luettavissa kuin vastaava kysely. Henkilökohtaisesti Pidän tässä esimerkissä vastaavasta kyselystä, koska se näyttää suoremmalta. Minun on helpompi nähdä, mitä lasketaan.
Mielestäni INNER JOIN on vähemmän suora. Ensin täytyy nähdä, että kaikki myyntitiedot-rivit palautetaan käsin ja sitten yhteenvetona. Et todellakaan saa tätä ennen kuin olet lukenut koko lausunnon.
Kumpi on parempi?
Kerro minulle mielipiteesi. Haluaisin kuulla, haluatko mieluummin käyttää vastaavaa alakyselyä tai INNER JOIN -esimerkkiä.