Inaamua SQL GROUP BY

合计函数 (比如 SUM) 常常需要添加 GROUP BY 语句。

GROUP BY 语句

GROUP BY 语句用于结合合计函数，根据一个或多个列对结果集进行分组。

Mfano wa SQL GROUP BY

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name

Mfano wa SQL GROUP BY

Tunianwemoja hii "Orders" tablio:

O_Id OrderDate OrderPrice Customer
1 2008/12/29 1000 Bush
2 2008/11/23 1600 Carter
3 2008/10/05 700 Bush
4 2008/09/28 300 Bush
5 2008/08/06 2000 Adams
6 2008/07/21 100 Carter

Sasa, tunatenda kutafuta jumla ya mabaki ya kila mwanasheria (jumla ya mambo ya mkataba).

Tunatenda kutumia kikata ya GROUP BY kwa kumpatikana kwa wengi wa wakati.

Tunatumia ujumbe wa SQL hii:

SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer

Matokeo wa kusoma hii inahusiana kama hii:

Customer SUM(OrderPrice)
Bush 2000
Carter 1700
Adams 2000

Inaangazia, ni kweli, nai?

Tunapenda kuangalia mambo ya kuzingatia kama itakuwa kama hii:

SELECT Customer,SUM(OrderPrice) FROM Orders

Matokeo wa kusoma hii inahusiana kama hii:

Customer SUM(OrderPrice)
Bush 5700
Carter 5700
Bush 5700
Bush 5700
Adams 5700
Carter 5700

Matokeo wa kusoma hii haikuwa tunayotarajia.

Kwa nini hatuwezi kutumia ujumbe wa SELECT hii? Kuonyesha hii: Ujumbe wa SELECT hii unaangalia kolumni mbili (Customer na SUM(OrderPrice)). "SUM(OrderPrice)" inatuma mkono moja (jumla ya kolumni "OrderPrice"), na "Customer" inatuma viwango vitano (kwa kila ukweli kwenye jadili ya "Orders"). Kwa hivyo, hatutakuwa na matokeo ya kawaida. Hata hivyo, tumekuwa na ujumbe wa GROUP BY ambao walendelea kusaidia kuzingatia hii.

GROUP BY kimoja sana ya kolumni

Tunaweza kuitumia kikata ya GROUP BY kwa kimoja sana ya kolumni, kama hii:

SELECT Customer,OrderDate,SUM(OrderPrice) FROM Orders
GROUP BY Customer,OrderDate