Hauv Microsoft Access, GROUP BY yog ib nqe lus uas koj tuaj yeem siv los ua ke cov ntaub ntawv nrog cov txiaj ntsig zoo ib yam hauv ib thaj chaw tshwj xeeb hauv ib cov ntaub ntawv. Yog tias koj suav nrog SQL aggregate muaj nuj nqi hauv SELECT nqe lus, xws li AVG, COUNT, lossis SUM, Access tsim cov ntsiab lus rau txhua cov ntaub ntawv.
Cov lus qhia hauv kab lus no siv rau Kev Nkag Mus rau Microsoft 365, Access 2019, 2016, 2013, thiab 2010.
Siv pab pawg BY
Koj tuaj yeem nrhiav thiab siv GROUP BY muaj nuj nqi siv cov lus nug SQL hauv SQL View. Nws yog ib txoj hauv kev yooj yim tshaj plaws thiab ncaj qha mus saib thiab tswj koj cov ntaub ntawv.
-
Pib Nkag mus thiab qhib koj cov ntaub ntawv.
Qhov piv txwv no siv Northwind Sample Database.
-
Xaiv lub Create tab.
Image -
Nyob rau hauv pawg lus nug, xaiv Qhia Tsim.
Image -
Nyob hauv Ntxiv Tables npe, xaiv lub rooj uas koj xav ua haujlwm nrog.
Image -
Xaiv View hauv pawg Cov txiaj ntsig thiab xaiv SQL Saib.
Image - Lub cev tseem ceeb yuav hloov mus rau lub qhov rais lus nug. Ntawm no, koj tuaj yeem sau cov lus nug uas koj nyiam.
-
Txhawm rau kom tau txais kev sib koom ua ke los ntawm SQL, koj yuav tsum sau qee yam zoo li no:
SELECTLos ntawm lub npe lub npe nyob qhov twg kab / pawg nyiam ' nkag';
Hloov lub npe tiag tiag ntawm lub rooj, qeb lossis kab ntawv kab lus, thiab tus nqi nkag tiag uas koj tab tom nrhiav.
Xav txog, piv txwv li, daim ntawv xaj cov ntaub ntawv uas muaj cov yam ntxwv hauv qab no:
- OrderID: Tus lej tus nqi tshwj xeeb txheeb xyuas txhua qhov kev txiav txim. Daim teb no yog tus yuam sij rau database.
- Salesperson: Tus nqi ntawv muab lub npe ntawm tus neeg muag khoom uas muag cov khoom. Daim teb no yog tus yuam sij txawv teb chaws rau lwm lub rooj uas muaj cov ntaub ntawv neeg ua haujlwm.
- CustomerID: Tus lej tus nqi sib raug rau tus lej tus neeg siv khoom. Daim teb no tseem yog tus yuam sij txawv teb chaws, hais txog lub rooj uas muaj cov ntaub ntawv tus neeg siv khoom.
- Revenue: Tus lej tus nqi sib raug rau tus nqi ntawm qhov muag.
Thaum nws los txog lub sijhawm los tshuaj xyuas kev ua haujlwm rau cov neeg muag khoom, Lub Rooj Txiav Txim muaj cov ntaub ntawv tseem ceeb uas yuav raug siv rau qhov kev tshuaj xyuas ntawd. Thaum ntsuas Jim, koj tuaj yeem, piv txwv li, sau cov lus nug yooj yim uas khaws tag nrho Jim cov ntaub ntawv muag:
XaivLos ntawm kev xaj qhov twg tus neeg muag khoom nyiam 'Jim';
Qhov no yuav khaws tag nrho cov ntaub ntawv los ntawm cov ntaub ntawv sib raug rau kev muag khoom los ntawm Jim:
OrderID Salesperson CustomerID Revenue
12482 Jim 182 40000
12488 Jim 219 25000
12519 Jim 137 85000
12602 Jim 182 10 12741 Jim 155 90000
Koj tuaj yeem tshuaj xyuas cov ntaub ntawv no thiab ua qee qhov kev suav phau ntawv txhawm rau txhim kho kev txheeb xyuas kev ua tau zoo, tab sis qhov no yuav yog ib txoj haujlwm nyuaj uas koj yuav tau rov ua dua rau txhua tus neeg muag khoom hauv lub tuam txhab. Hloov chaw, koj tuaj yeem hloov txoj haujlwm no nrog ib qho GROUP BY cov lus nug uas suav txhua tus neeg muag khoom cov txheeb cais hauv lub tuam txhab. Koj sau cov lus nug thiab qhia meej tias cov ntaub ntawv yuav tsum tau pab pawg cov txiaj ntsig raws li Cov Neeg Muag Khoom. Tom qab ntawd koj tuaj yeem siv ib qho ntawm SQL aggregate functions los ua kev suav ntawm cov txiaj ntsig.
Ntawm no yog piv txwv. Yog tias koj ua tiav cov lus SQL nram qab no:
SELECT Tus neeg muag khoom, SUM(Cov Nyiaj Tau Los) AS 'Tag Nrho', MIN(Cov Nyiaj Tau Los) AS 'Smallest', MAX(Revenue) AS 'Large', AVG(Revenue) AS 'Average', COUNT(Revenue) AS ' Number' Los ntawm Orders GROUP BY Salesperson;
Koj yuav tau txais cov txiaj ntsig hauv qab no:
Tus Neeg Muag Khoom Total Smallest Largest Average Number
Jim 250000 10000 90000 50000 5
Mary 342000 24000 102000 57000 6 30 34180 3
Raws li koj tuaj yeem pom, qhov ua haujlwm muaj zog no tso cai rau koj los tsim cov lus qhia luv luv los ntawm hauv cov lus nug SQL, muab kev txawj ntse ua lag luam muaj txiaj ntsig rau tus thawj tswj xyuas kev ua haujlwm. GROUP BY clause feem ntau siv rau hauv cov ntaub ntawv rau lub hom phiaj no thiab yog ib qho cuab yeej tseem ceeb hauv DBA lub hnab dag.
