sqlÖÐgroupbyµÄÓ÷¨
group by ×Ӿ佫Êý¾Ý¼¯°´Ö¸¶¨ÁзÖ×é²¢¾ÛºÏÿ¸ö×éµÄÖ¸¶¨Öµ£¬Óï·¨£ºselect aggregate_function(column_name) from table_name group by column_name¡£ÆäÓ÷¨°üÀ¨£º1. ¾ÛºÏÊý¾Ý£¨ÅÌËãÿ¸ö×éµÄ×ܺ͡¢Æ½¾ùÖµµÈ£©£»2. ·Ö×éÊý¾Ý£¨½«Êý¾Ý°´Ö¸¶¨Áл®·ÖΪ×飩£»3. ¹ýÂËÊý¾Ý£¨ÍŽá having ×Ӿ䣩¡£
GROUP BY Óï·¨
GROUP BY ×Ӿ佫Êý¾Ý¼¯°´Ö¸¶¨Ã¶¾ÙÐзÖ×飬²¢¾ÛºÏÿ¸ö×éµÄÖ¸¶¨Öµ¡£ÆäÓï·¨ÈçÏ£º
SELECT aggregate_function(column_name) FROM table_name GROUP BY column_name
µÇ¼ºó¸´ÖÆ
ÆäÖУº
aggregate_function£º¾ÛºÏº¯Êý£¬Èç SUM(), COUNT(), AVG(), MAX(), MIN() µÈ¡£
column_name£ºÓÃÓÚ·Ö×éµÄÁС£
Ó÷¨
GROUP BY µÄÖ÷ÒªÓ÷¨°üÀ¨£º
¾ÛºÏÊý¾Ý£ºÅÌËãÿ¸ö×éµÄ¾ÛºÏÖµ£¨×ܺ͡¢Æ½¾ùÖµ¡¢×î´óÖµ¡¢×îСֵµÈ£©¡£
·Ö×éÊý¾Ý£º½«Êý¾Ý°´Ö¸¶¨Áл®·ÖΪ×飬ÒÔ±ã¶Ôÿ¸ö×éµÄÊý¾Ý¾ÙÐÐÆÊÎö¡£
¹ýÂËÊý¾Ý£ºÍŽá HAVING ×Ӿ䣬¶Ô·Ö×éºóµÄÊý¾Ý¾ÙÐйýÂË¡£
ʾÀý
¼ÙÉèÎÒÃÇÓÐÒ»¸öÃûΪ Sales µÄ±í£¬°üÀ¨ÒÔÏÂÊý¾Ý£º
OrderID | Product | Category | Price |
---|---|---|---|
1 | Product A | Category 1 | 10 |
2 | Product A | Category 1 | 15 |
3 | Product B | Category 2 | 20 |
4 | Product C | Category 3 | 30 |
ʾÀý 1£ºÅÌËãÿ¸öÖÖ±ð²úÆ·µÄ×ܼÛ
SELECT Category, SUM(Price) AS TotalPrice FROM Sales GROUP BY Category;
µÇ¼ºó¸´ÖÆ
Ч¹û£º
Category | TotalPrice |
---|---|
Category 1 | 25 |
Category 2 | 20 |
Category 3 | 30 |
ʾÀý 2£º°´²úÆ··Ö×飬ͳ¼Æ¶©µ¥Êý
SELECT Product, COUNT(*) AS OrderCount FROM Sales GROUP BY Product;
µÇ¼ºó¸´ÖÆ
Ч¹û£º
Product | OrderCount |
---|---|
Product A | 2 |
Product B | 1 |
Product C | 1 |
ÒÔÉϾÍÊÇsqlÖÐgroupbyµÄÓ÷¨µÄÏêϸÄÚÈÝ£¬¸ü¶àÇë¹Ø×¢±¾ÍøÄÚÆäËüÏà¹ØÎÄÕ£¡