Kawm Txog Siv Microsoft Access GROUP BY Query

Koj siv tau cov queries yooj yim SQL retrieve cov ntaub ntawv los ntawm ib tug database tab sis qhov no feem ntau tsis muab txaus txawj ntse kom tau raws li kev ua lag luam txoj cai. SQL kuj tseem qhia koj txog qhov kev muaj peev xwm los koom tau cov lus nug cov lus nug raws li cov kev sib tw hauv qib kom thiaj li siv cov lus sib sau ua ke nrog pawg GROUP los ntawm tsab cai dab tsi. Xav txog, piv txwv li, tsab ntawv teev cov lus qhia uas muaj cov cwj pwm hauv qab no:

• OrderID - tus lej numeric tus nqi txheeb xyuas txhua qhov kev txiav txim. Daim teb no yog thawj qhov tseem ceeb rau lub database.
• Tus neeg muag khoom - tus nqi ntawv muab lub npe ntawm tus neeg muag khoom muag uas muag cov khoom. Daim teb no yog qhov tseem ceeb txawv teb chaws rau lwm lub rooj uas muaj cov neeg qhia ntaub ntawv.
• CustomerID - tus lej numeric piv rau tus qhua tuaj noj nyiaj tus account. Daim teb no kuj yog ib qhov tseem ceeb txawv teb chaws, referencing ib lub rooj muaj cov ntaub ntawv qhia txog tus neeg.
• Revenue - tus lej numeric nqi sib piv rau tus nqi nyiaj ntawm qhov muag.

Thaum nws los txog sij hawm xyuas kev ua haujlwm rau cov neeg muag khoom, Orders lub rooj muaj cov ntaub ntawv tseem ceeb uas yuav muab siv rau qhov kev tshuaj xyuas ntawd. Thaum ntsuas Jim, koj ua tau, piv txwv, sau ib lo lus nug yooj yooj yim uas muab tag nrho Jim cov ntawv muag khoom khaws tseg:

XAIV * LOS SIS XWS LI QHOV CHAW LI CAS Neeg Zoo Siab 'Jim'

Qhov no yuav muab tag nrho cov ntaub ntawv khaws tseg los ntawm cov ntaub ntawv sib thooj rau kev muag khoom los ntawm Jim:

OrderID Neeg Muag Khoom Muag Khoom Muag Nyiaj Tau Los 12482 Jim 182 40000 12488 Jim 219 25000 12519 Jim 137 85000 12602 Jim 182 10000 12741 Jim 155 90000

Koj tuaj yeem soj ntsuam cov ntaub ntawv no thiab ua qee cov lus teb kom tau los nrog kev ua haujlwm zoo, tab sis qhov no yuav yog ib txoj haujlwm tseem ceeb uas koj yuav tau rov ua dua rau txhua tus neeg muag khoom hauv lub tuam txhab. Xwb, koj tuaj yeem hloov txoj haujlwm no nrog ib tus GROUP los ntawm cov lus nug uas suav cov txheeb cais rau txhua tus neeg muag khoom hauv lub tuam txhab. Koj tsuas sau cov lus nug thiab qhia kom meej tias cov ntaub ntawv database yuav tsum tau soj ntsuam cov ntsiab lus raws li Daim Teb Muag. Koj mam li siv ib qho ntawm cov kev sib sau ua ke SQL los ua cov lus teb ntawm cov txiaj ntsig.

Nov yog ib qho piv txwv. Yog tias koj sau cov lus nram qab no SQL:

SELECT SALES, SUM (Revenue) AS 'Total', MIN (Revenue) AS 'Smallest', MAX (Revenue) AS 'Largest', AVG (Revenue) AS 'Average', COUNT (Revenue) AS 'Naj Npawb' NTAWM txiav txim siab GROUP Los ntawm tus neeg muag khoom

Koj yuav tau txais cov ntsiab lus nram qab no:

Tus neeg muag khoom Ntaus Yam tsawg kawg tshaj plaws Naj npawb Jim 250000 10000 90000 50000 5 Mary 342000 24000 102000 57000 6 Bob 118000 4000 36000 39333 3

Raws li koj tau pom, qhov kev ua haujlwm ntawm no muaj zog rau koj los tsim cov lus qhia me me los ntawm cov lus nug SQL, muab kev lag luam tseem ceeb rau tus thawj tswj xyuas kev txheeb xyuas. Pawg GROUP los ntawm cov lus siv feem ntau yog siv rau hauv cov ntaub ntawv khaws tseg rau lub hom phiaj no thiab yog ib qho cuab yeej tseem ceeb hauv DBA lub hnab ntawm kev xav.