SQLÓï¾ä£ºGroup By Óï¾äС½á
1. Group By Óï¾ä¼ò½é£º
Group ByÓï¾ä´ÓÓ¢ÎĵÄ×ÖÃæÒâÒåÉÏÀí½â¾ÍÊÇ“¸ù¾Ý(by)Ò»¶¨µÄ¹æÔò½øÐзÖ×é(Group)”¡£ËüµÄ×÷ÓÃÊÇͨ¹ýÒ»¶¨µÄ¹æÔò½«Ò»¸öÊý¾Ý¼¯»®·Ö³ÉÈô¸É¸öСµÄÇøÓò£¬È»ºóÕë¶ÔÈô¸É¸öСÇøÓò½øÐÐÊý¾Ý´¦Àí¡£
P.S. ÕâÀïÕæÊÇÌå»áµ½ÁËÒ»¸öºÃµÄÃüÃûµÄÁ¦Á¿£¬Group By´Ó×ÖÃæÊÇÖ±½ÓÈ¥Àí½âÊǷdz£ºÃÀí½âµÄ¡£¶÷£¬ÒÔºóÔÚÃüÃûµÄ»·½ÚÒ»¶¨Òª¼Ó°Ñ¾¢:)¡£»°Ì⳶ԶÁË¡£
2. Group By µÄʹÓãº
ÉÏÃæÒѾ¸ø³öÁ˶ÔGroup ByÓï¾äµÄÀí½â¡£»ùÓÚÕâ¸öÀí½âºÍSQL Server 2000µÄÁª»ú°ïÖú£¬ÏÂÃæ¶ÔGroup ByÓï¾äµÄ¸÷ÖÖµäÐÍʹÓýøÐÐÒÀ´ÎÁоÙ˵Ã÷¡£
2.1 Group By [Expressions]:
Õâ¸ö¿ÖÅÂÊÇGroup ByÓï¾ä×î³£¼ûµÄÓ÷¨ÁË£¬Group By + [·Ö×é×Ö¶Î](¿ÉÒÔÓжà¸ö)¡£ÔÚÖ´ÐÐÁËÕâ¸ö²Ù×÷ÒÔºó£¬Êý¾Ý¼¯½«¸ù¾Ý·Ö×é×ֶεÄÖµ½«Ò»¸öÊý¾Ý¼¯»®·Ö³É¸÷¸ö²»Í¬µÄС×é¡£±ÈÈçÓÐÈçÏÂÊý¾Ý¼¯£¬ÆäÖÐË®¹ûÃû³Æ(FruitName)ºÍ³ö²ú¹ú¼Ò(ProductPlace)ΪÁªºÏÖ÷¼ü£º
FruitName ProductPlace Price
Apple China $1.1
Apple Japan $2.1
Apple USA $2.5
Orange China $0.8
Banana China $3.1
Peach USA $3.0
Èç¹ûÎÒÃÇÏëÖªµÀÿ¸ö¹ú¼ÒÓжàÉÙÖÖË®¹û£¬ÄÇôÎÒÃÇ¿ÉÒÔͨ¹ýÈçÏÂSQLÓï¾äÀ´Íê³É£º
SELECT COUNT(*) AS Ë®¹ûÖÖÀà, ProductPlace AS ³ö²ú¹ú
from T_TEST_FRUITINFO
GROUP BY ProductPlaceÕâ¸öSQLÓï¾ä¾ÍÊÇʹÓÃÁËGroup By + ·Ö×é×ֶεķ½Ê½£¬ÄÇôÕâ¾äSQLÓï¾ä¾Í¿ÉÒÔ½âÊͳɓÎÒ°´ÕÕ³ö²ú¹ú¼Ò(ProductPlace)½«Êý¾Ý¼¯½øÐзÖ×飬Ȼºó·Ö±ð°´ÕÕ¸÷¸ö×éÀ´Í³¼Æ¸÷×ԵļǼÊýÁ¿¡£”ºÜºÃÀí½â¶Ô°É¡£ÕâÀïÖµµÃ×¢ÒâµÄÊǽá¹û¼¯ÖÐÓÐÁ½¸ö·µ»Ø×ֶΣ¬Ò»¸öÊÇProductPlace(³ö²ú¹ú), Ò»¸öÊÇË®¹ûÖÖÀà¡£Èç¹ûÎÒÃÇÕâÀïË®¹ûÖÖÀ಻ÊÇÓÃCount(*)£¬¶øÊÇÀàËÆÈçÏÂд·¨µÄ»°£º
SELECT FruitName, ProductPlace from T_TEST_FRUITINFO GROUP BY ProductPlaceÄÇôSQLÔÚÖ´ÐдËÓï¾äµÄʱºò»á±¨ÈçϵÄÀàËÆ´íÎó£º
Ñ¡ÔñÁбíÖеÄÁÐ 'T_TEST_FRUITINFO.FruitName' ÎÞЧ£¬ÒòΪ¸ÃÁÐûÓаüº¬ÔھۺϺ¯Êý»ò GROUP BY ×Ó¾äÖС£Õâ¾ÍÊÇÎÒÃÇÐèҪעÒâµÄÒ»µã£¬Èç¹ûÔÚ·µ»Ø¼¯×Ö¶ÎÖУ¬ÕâЩ×Ö¶ÎҪô¾ÍÒª°üº¬ÔÚGroup ByÓï¾äµÄºóÃ棬×÷Ϊ·Ö×éµÄÒÀ¾Ý£»ÒªÃ´¾ÍÒª±»°üº¬ÔھۺϺ¯ÊýÖС£ÎÒÃÇ¿ÉÒÔ½«Group By²Ù×÷ÏëÏó³ÉÈçϵÄÒ»¸ö¹ý³Ì£¬Ê×ÏÈϵͳ¸ù¾ÝSELECT Óï¾äµÃµ½Ò»¸ö½á¹û¼¯£¬Èç×ʼµÄÄǸöË®¹û¡¢³ö²ú¹ú¼Ò¡¢µ¥¼ÛµÄÒ»¸öÏêϸ±í¡£È»ºó¸ù¾Ý·Ö×é×ֶΣ¬½«¾ßÓÐÏàͬ·Ö×é×ֶεļǼ¹
Ïà¹ØÎĵµ£º
SQL ServerÈ«ÎÄË÷ÒýµÄ¸öÈË×ܽá(ÉÏ) -¹ØÓÚÖÐÎÄ·Ö´Ê
(2005-11-14 04:29:44)
תÔØ
·ÖÀࣺÉî¶ÈÑо¿
´ó¼Ò¶¼ÖªµÀLIKE²éѯºÜÂý£¬È«ÎÄË÷Òý¾ÍÊÇÊÂÏÈ×öºÃÏà¹ØµÄË÷Òý£¬±íʾÄĸöÖ÷Ìâ´Ê¿ÉÒÔÔÚÄÄЩ¼Ç¼ÀïÕÒµ½£¬ÉõÖÁÊÂÏȼÆËãºÃRANK£¬¼ìË÷ʱ¿ÉÒÔ°ÑÏà¹Ø¶È¸ßµÄÏÈÁгöÀ´£¬Õâ¿ÉÒÔ´ó´óÌá¸ß¼ìË÷µÄËٶȡ£
´ò¸ö±È·½£¬ÄãÓÐ ......
MS SQL Server 2008 ÔÚ½¨Íê±íºó£¬Èç¹ûÒª²åÈëÈÎÒâÁУ¬ÔòÌáʾ£º
µ±Óû§ÔÚÔÚSQL Server 2008ÆóÒµ¹ÜÀíÆ÷Öиü¸Ä±í½á¹¹Ê±£¬±ØÐëÒªÏÈɾ³ýÔÀ´µÄ±í£¬È»ºóÖØд´½¨ÐÂ±í£¬²ÅÄÜÍê³É±íµÄ¸ü¸Ä£¬Èç¹ûÇ¿Ðиü¸Ä»á³öÏÖÒÔÏÂÌáʾ£º²»ÔÊÐí±£´æ¸ü¸Ä¡£ÄúËù×öµÄ¸ü¸ÄÒªÇóɾ³ý²¢ÖØд´½¨ÒÔÏÂ±í¡£Äú¶ÔÎÞ·¨ÖØд´½¨µÄ±ê½øÐÐÁ˸ü¸Ä»òÕßÆôÓÃÁË“× ......
SQLÖÐround£¨£©º¯ÊýÓ÷¨
SQL round()Ïê½â
roundÓÐÁ½¸öÖØÔØ,Ò»¸öÓдøÓÐÁ½¸ö²ÎÊýµÄ,Ò»¸öÊÇ´øÓÐÈý¸ö²ÎÊýµÄ,
ÿһ¸ö²ÎÊý¶¼ÏàͬÊÇÒª´¦ÀíµÄÊý,
1.´øÓÐÁ½¸ö²ÎÊý.ÿ¶þ¸ö²ÎÊýÊÇСÊýµãµÄ×ó±ßµÚ¼¸Î»»òÓұߵڼ¸Î»,·Ö±ðÓÃÕý¸º±íʾ.×ó±ßΪ¸º,ÓÒ±ßΪ¸º.ΪËÄÉáÎåÈë.
select round(748.585929,-1) 750.000000
select round(748.58592 ......
¾ÛºÏº¯ÊýÈôÒª»ã×ÜÒ»¶¨·¶Î§µÄÊýÖµ£¬ÇëʹÓÃÒÔϺ¯Êý£º
SUM
·µ»Ø±í´ïʽÖÐËùÓÐÖµµÄ×ܺ͡£
Óï·¨
SUM(aggregate)
SUM Ö»ÄÜÓë°üº¬ÊýÖµµÄ×Ö¶ÎÒ»ÆðʹÓ᣽«ºöÂÔ¿ÕÖµ¡£
AVERAGE
·µ»Ø±í´ïʽÖÐËùÓзǿÕÖµµÄƽ¾ùÖµ£¨ËãÊõƽ¾ùÖµ£©¡£
Óï·¨
AVERAGE(aggregate)
AVERAGE Ö»ÄÜÓë°üº¬ÊýÖµµÄ×Ö¶ÎÒ»ÆðʹÓ᣽«ºöÂÔ¿ÕÖµ¡£
......