Funda Ngokusebenzisa i-Microsoft Access GROUP BY Umbuzo

Ungasebenzisa imibuzo eyisisekelo ye- SQL ukuthola idatha kusuka kumininingwane kodwa lokhu ngokuvamile akuhlinzeki ngokuhlakanipha okwanele ukuhlangabezana nezidingo zebhizinisi. I-SQL iphinde inikeze ikhono lokuqoqa imiphumela yombuzo ngokusekelwe emikhwalweni yezinga le-irowu ukuze usebenzise imisebenzi ehlanganisiwe usebenzisa isigatshana se-GROUP BY. Cabanga, isibonelo, ithebula lwedatha yedatha elinezici ezingezansi:

• I-odaID - inani lezinombolo likhomba kuphela ukuhlelwa kwe-oda ngalinye. Le nsimu iyinhloko eyinhloko yedatha.
• Umthengisi - inani lokubhala elihlinzeka igama lomthengisi othengisa imikhiqizo. Le nsimu yikhiye langaphandle kwelinye itafula eliqukethe imininingwane yabasebenzi.
• I-CustomerID - inani lezinombolo elihambisana nenombolo ye-akhawunti yekhasimende. Le nsi futhi iyisihluthulelo sangaphandle, ichaza ithebula eliqukethe ulwazi lwe-akhawunti yekhasimende.
• Imali engenayo - inani lezinombolo elihambisana nedola lemali yokudayiswa.

Uma kufika isikhathi sokuqhuba ukubuyekezwa kokusebenza kwabadayisi, ithebula le-oda liqukethe ukwaziswa okubalulekile okungasetshenziselwa ukubuyekezwa. Uma uhlola uJim, ungabhala, isibonelo, ukubhala umbuzo olula othola wonke amarekhodi okuthengisa kaJim:

HLOLA * OKUSUKA KWE-oda OKUPHAKANYWA KWE-Salesperson NJENGOBA 'UJim'

Lokhu kuzokhipha wonke amarekhodi avela ku-database ehambisana nokudayiswa okwenziwa nguJim:

Umthengisi we-OrderID we-CustomerID Revenue 12482 Jim 182 40000 12488 Jim 219 25000 12519 Jim 137 85000 12602 Jim 182 10000 12741 Jim 155 90000

Ungabuyekeza lolu lwazi futhi wenze ezinye izibalo zomsebenzi ukuze uthole izibalo zokusebenza, kodwa lokhu kungaba umsebenzi obalulekile ukuthi uzodinga ukuphinda umthengisi ngamunye enkampanini. Esikhundleni salokho, ungashintsha lo msebenzi ngombuzo owodwa we-GROUP BY obala izibalo kumthengisi ngamunye enkampanini. Uvele ubhale umbuzo bese ucacisa ukuthi i-database kufanele iqoqe imiphumela esekelwe kumkhakha we-Salesperson. Ungasebenzisa noma yimiphi imisebenzi ye-SQL ngokubanzi ukwenza izibalo emiphumeleni.

Nasi isibonelo. Uma wenza isitatimende esilandelayo se-SQL:

Hlola ukuthengisa, i-MIN (Imali engenayo) AS 'Incane', i-MAX (Imali engenayo) AS 'Inkulu kakhulu', i-AVX (Imali engenayo) AS 'Isilinganiso', COUNT (Imali engenayo) AS 'Inombolo' KUSuka ku-oda GROUP BY Salesperson

Uzothola imiphumela elandelayo:

Umthengisi Ophelele Inani Elikhulu Kunazo Zonke Inani Eliphakathi Kakhulu UJ 250000 10000 90000 50000 5 UMariya 342000 24000 102000 57000 6 Bob 118000 4000 36000 39333 3

Njengoba ungabona, lo msebenzi onamandla ikuvumela ukuba ukhiphe imibiko encane esuka ngaphakathi kombuzo we-SQL, unikeze ukuhlakanipha kwebhizinisi okubalulekile kumphathi oqhuba ukubuyekezwa kokusebenza. Isigatshana se-GROUP BY sivame ukusetshenziswa ezinqolobaneni zale njongo futhi kuyithuluzi elibalulekile esikhwameni se-DBA sika-tricks.