E aʻo e pili ana i ka hoʻohanaʻana i ka hua'ōlelo Microsoft Access GROUP ma o ka hulina

Hiki iāʻoe ke hoʻohana i nā'atika SQL manuahi e kiʻihoʻi mai i kaʻikepili mai kahi pūnaewele akāʻaʻole kēia manawa e hāʻawi i kaʻike kūpono no ka hālāwaiʻana i nā koiʻoihana. Hoʻomaopopoʻo SQL iāʻoe i ka hiki ke hoʻohui i nā hualoaʻa hulina ma luna o nā māhele o ka paepae maʻamau e hiki ai ke hoʻohana i nā hana i hoʻohuiʻia me ka hoʻohanaʻana i ka GROUP BY ka papa. E noʻonoʻo, i kekahi laʻana, he papa heluʻike i kauʻia ma lalo:

• OrderID - kahi helu helu eʻike maopopo i kēlā me kēia kauoha. ʻO kēia māhele ke kumu nui o ka pūnaewele.
• Luna Nui - he helu helu e hāʻawi ana i ka inoa o ke kanaka kūʻai aku nāna i kūʻai aku i nā huahana. ʻO kēia māhele he kī'ē aʻe i kekahi papa'ē aʻe i loko o kaʻike kanaka.
• Wahi'Aiwi - he helu helu e like me ka helu helu pūʻulu. He kī'ē aʻe kēia māhele, e kuhikuhi ana i kahi papa e waiho nei i kaʻikepili pūnaewele.
• Loaʻa - he helu waiwai e like me ke kālā o ke kūʻai.

Ke hiki mai ka manawa e hana i nā hōʻike hana no nā mea kūʻai, ua loaʻa i ka papaʻaina nāʻike kūpono e hoʻohanaʻia no ia hōʻike. Ke hoʻohālikelikeʻiaʻo Jim, hiki iāʻoe, no ka mea, ke kākau i kahi hulina maʻalahi e lawe i nā mea kūʻai a pau a Jim:

SELECTION * MAI nā kauoha WHERE Lunaʻakoʻa LIKE 'Jim'

E lawe i kēia mau moʻolelo mai ka waihonaʻikepili e like me nā kūʻai i hanaʻia e Jim:

Orderid Salesperson Customer Customer Revenue 12482 Jim 182 40000 12488 Jim 219 25000 12519 Jim 137 85000 12602 Jim 182 10000 12741 Jim 155 90000

Hiki iāʻoe ke noʻonoʻo i kēiaʻike a hana i kekahi mau papa kuhikuhi no ka hōʻeaʻana me nā'ikepiliheluʻoihana, akā, he hana koʻikoʻi kēia e pono aiʻoe e hana hou no kēlā me kēia mea kūʻai aku i ka hui. Ma kahi hakahaka, hiki iāʻoe ke pani i kēia hana me kahi helu GROUP BY query e helu ana i nā helu helu no kēlā me kēia mea kūʻai aku i ka hui. E kākau waleʻoe i ka hulina a hoʻonohonoho pono e hoʻonohonoho i kaʻikepili i nā hopena e pili ana ma ka māhele Salesman. Hiki iāʻoe ke hoʻohana i kekahi o nā hana hōʻuluʻulu SQL no ka hanaʻana i nā helu ma nā hopena.

Eia kekahi hiʻohiʻona. Inā hoʻokōʻoe i ka leka SQL penei:

NĀ KĀINA SELELE, SUM ('Ake kālā) AS 'Kino loa', MAX ('Ake kālā) AS 'Largest', AVG (Revenue) AS 'Average', COUNT ('Ake kālā) AS 'Number' FROM Orders GROUP Na ka mea kūʻai

Loaʻa iāʻoe nā hualoaʻa i lalo:

Luna Nui Luna Nui'Alepeke Ka helu nui loa Jim 250000 10000 90000 50000 5 Mary 342000 24000 102000 57000 6 Bob 118000 4000 36000 39333 3

E like me kāu eʻike ai,ʻo kēia hana mana eʻae iāʻoe e hoʻokuʻu i nā pūʻulu liʻiliʻi mai loko o kahi hulina SQL, e hāʻawi ana i kaʻikeʻoihana waiwai i ka mea nāna e mālama i nā hōʻike hōʻike. Hoʻohana pinepineʻia ka papahana ma nā kahua kikowaena no kēia kumuhana a he mea waiwaiʻo ia i ka pahu o DBA.