sqlÖеÄgroup byÓ÷¨
sqlÖеÄgroup byÓï¾ä½«Êý¾Ý¼¯°´Ö¸¶¨ÁзÖ×飬²¢¶Ô¸÷×é¾ÙÐоۺÏÅÌËã¡£Ó÷¨ÈçÏ£ºÊ¶±ð·Ö×éÁУºÖ¸¶¨Òª·Ö×éµÄÁС£¾ÛºÏº¯Êý£ºÊ¹ÓÃsum¡¢count¡¢avgµÈº¯Êý¶Ô·Ö×éÖµÅÌËã¡£·Ö×éЧ¹û£ºÅÌÎÊ·µ»Ø·Ö×éЧ¹û£¬ÏÔʾÿһ×éµÄ¾ÛºÏÅÌËãÖµ¡£
SQL ÖÐµÄ GROUP BY Ó÷¨
SQL ÖÐµÄ GROUP BY Óï¾äÓÃÓÚ½«Êý¾Ý¼¯ÖеÄÐзÖ×飬²¢Æ¾Ö¤·Ö×é¶ÔÊý¾Ý¾ÙÐоۺÏÅÌËã¡£Ëüͨ¹ý½«¾ßÓÐÏàֵͬµÄÌض¨ÁеÄÐзÖ×éÀ´ÊÂÇé¡£
»ù±¾Óï·¨£º
SELECT aggregate_function(column_name) FROM table_name GROUP BY column_name
µÇ¼ºó¸´ÖÆ
Ó÷¨£º
ʶ±ð·Ö×éÁУºÊ¹Óà GROUP BY ×Ó¾äÖ¸¶¨Òª·Ö×éµÄÁС£
¾ÛºÏº¯Êý£ºÊ¹ÓþۺϺ¯Êý£¨Èç SUM¡¢COUNT¡¢AVG£©¶Ô·Ö×éÁÐÖеÄÖµ¾ÙÐÐÅÌËã¡£
·Ö×éЧ¹û£ºÅÌÎÊ·µ»Ø·Ö×éЧ¹û£¬ÆäÖÐÿһÐж¼ÌåÏÖÒ»¸ö×飬²¢ÏÔʾ¾ÛºÏÅÌËãЧ¹û¡£
ʾÀý£º
¼ÙÉèÎÒÃÇÓÐÒ»¸ö°üÀ¨Ñ§ÉúЧ¹ûµÄ±í£º
ѧºÅ | ÐÕÃû | Ч¹û |
---|---|---|
1 | John | 85 |
2 | Mary | 90 |
3 | John | 95 |
4 | Susan | 80 |
ÒÔÏÂÅÌÎʽ«Ñ§ÉúЧ¹û°´ÐÕÃû·Ö×飬²¢ÅÌËãÿ¸öѧÉúЧ¹ûµÄƽ¾ùÖµ£º
SELECT AVG(Ч¹û) FROM students GROUP BY ÐÕÃû
µÇ¼ºó¸´ÖÆ
Ч¹û£º
ÐÕÃû | ƽ¾ùЧ¹û |
---|---|
John | 90 |
Mary | 90 |
Susan | 80 |
×¢ÖØÊÂÏ
·Ö×éÁÐÖеÄÖµ±ØÐè¾ßÓÐÏàµÈµÄÊý¾ÝÀàÐÍ¡£
GROUP BY ×Ó¾ä±ØÐèÔÚ FROM ×Ó¾äÖ®ºó£¬ÔÚ HAVING ×Ó¾ä֮ǰ¡£
HAVING ×Ó¾ä¿ÉÓÃÓÚ¶Ô·Ö×éЧ¹û¾ÙÐнøÒ»²½¹ýÂË¡£
ÒÔÉϾÍÊÇsqlÖеÄgroup byÓ÷¨µÄÏêϸÄÚÈÝ£¬¸ü¶àÇë¹Ø×¢±¾ÍøÄÚÆäËüÏà¹ØÎÄÕ£¡