Funda Ngokusebenzisa iMicrosoft Access GROUP BY Query

Ungasebenzisa imibuzo eyisiseko ye- SQL ukufumana idatha esuka kwisiseko sedatha kodwa oku kaninzi akuboneleli ngeenkcukacha ezaneleyo zokuhlangabezana neemfuno zoshishino. I-SQL ikwabonelela ngekhono lokuqhelanisa iziphumo zombuzo ngokusekelwe kwiimpawu zamanqanaba emigangatho ukwenzela ukuba isebenze imisebenzi esebenzayo usebenzisa igatya le-GROUP BY. Cinga, umzekelo, umyalelo wetafile yedatha equkethe iimpawu ezi ngezantsi:

• I-OrderID - ixabiso leenombolo ngokukhawuleza ukuchonga umyalelo ngamnye. Le nsiyona yintloko ephambili kwisiseko sedatha.
• I-Salesperson - ixabiso lokubhaliweyo elibonelela igama lomthengisi othengisa iimveliso. Le ntsi yikhiye yangaphandle kwelinye itafile equkethe ulwazi lomsebenzi.
• Umthengi - inani lexabiso elihambelana nenombolo yeakhawunti yabathengi. Le ntsi nayo yikhiye wangaphandle, ibhekisela itafile eneenkcukacha zeakhawunti yabathengi.
• Ingeniso - ixabiso lexabiso elihambelana nexabiso lemali edayiswayo.

Xa kufika ixesha lokuqhuba uvavanyo lwentsebenzo yabathengisi, itheyibhile ye-Orders iqulethe ulwazi oluxabisekileyo olungasetyenziselwa ukuhlaziywa. Xa uhlola uJim, unokwenza, umzekelo, ubhale umbuzo olula ofumana zonke iirekhodi zokuthengisa zikaJim:

UKUKHETHA * OKUSUKA KOMTHETHO OLAWULO WENTENTI NJENGOBA 'UJim'

Oku kuya kuzuza zonke iirekhodi ezivela kwisiseko sedatha ehambelana nokuthengisa okwenziwa nguJim:

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

Unokuyihlolisisa le ngcaciso kwaye wenze ezinye izibalo zomsebenzi ukuza kunye nenani lokusebenza, kodwa oku kuya kuba ngumsebenzi onzima ukuba uza kuphinda uphinde umthengisi ngamnye kwinkampani. Endaweni yoko, ungayenza indawo yalo msebenzi ngombuzo owodwa we-GROUP NGUBA obala i-statistics kumthengisi ngamnye kwinkampani. Ubhala nje umbuzo uze ucacise ukuba i-database kufuneka iqela iziphumo ezisekelwe kwintsimi yabathengisi. Ungasebenzisa nayiphi na imisebenzi ye-SQL ngokubanzi ukwenza izibalo kwiziphumo.

Nasi umzekelo. Ukuba wenze le SQL isitatimende esilandelayo:

UKUKHETHA UMTHETHO WOKUSEBENZISWA KWE-SUM (Imali engenayo) I-AS 'Total', MIN (Imali engenayo) AS 'Encinci', MAX (Imali engenayo) AS 'enkulu kakhulu', AVG (Imali engenayo) AS 'Umyinge', COUNT (Imali) AS 'Inombolo' BY Salesperson

Uya kufumana iziphumo ezilandelayo:

I-Salesperson Iyonke Inqanaba elincinci kunazo zonke Inani likaJim 250000 10000 90000 50000 5 UMary 342000 24000 102000 57000 6 Bob 118000 4000 36000 39333 3

Njengoko ubona, lo msebenzi onamandla uvumela ukuba uvelise iingxelo ezincinci ngaphakathi kwimibuzo yeSQL, ukubonelela ngeengcaphephe zezoshishino kumphathi oqhuba ukuhlolwa kokusebenza. I-GROUP BY clause isetyenziswe rhoqo kwiinkcukacha zolwazi kule njongo kwaye isisityebi esibalulekileyo kwi-DBA isikhwama seendlela.