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£¬¼ìË÷ʱ¿ÉÒÔ°ÑÏà¹Ø¶È¸ßµÄÏÈÁгöÀ´£¬Õâ¿ÉÒÔ´ó´óÌá¸ß¼ìË÷µÄËٶȡ£
´ò¸ö±È·½£¬ÄãÓÐ ......
Ò»¸öÏîÄ¿Íê³ÉºóÊý¾Ý¿âÖлáÓкܶàÎÞÓõIJâÊÔÊý¾Ý£¬¿ÉÒÔʹÓÃdelete * ½«Êý¾ÝÈ«²¿É¾³ý£¬µ«×ÔÔö³¤ÁУ¨Ò»°ãÊÇÖ÷¼ü£©»ùÊý²»»á¹éÁ㣬ʹÓÃTRUNCATEº¯Êý¿ÉÒÔ½«±íÖÐÊý¾ÝÈ«²¿É¾³ý£¬²¢ÇÒ½«×ÔÔö³¤ÁлùÊý¹éÁã¡£Ò»¶¨Òª×¢Ò⣬±íÖеÄÊý¾ÝÈ«²¿É¾³ýÁË¡£ËüµÄÓï·¨ÈçÏ£º
TRUNCATE TABLE tableName –ÆäÖÐtableNameÖÐËùÒª²Ù×÷µÄÊý¾Ý
......
ÐÐÁÐת»»
create table test(id int,name varchar(20),quarter int,profile int)
insert into test values(1,'a',1,1000)
insert into test values(1,'a',2,2000)
insert into test values(1,'a',3,4000)
insert into test values(1,'a',4,5000)
insert into test values(2,'b',1,3000)
insert into test values(2, ......
SQLÖÐround£¨£©º¯ÊýÓ÷¨
SQL round()Ïê½â
roundÓÐÁ½¸öÖØÔØ,Ò»¸öÓдøÓÐÁ½¸ö²ÎÊýµÄ,Ò»¸öÊÇ´øÓÐÈý¸ö²ÎÊýµÄ,
ÿһ¸ö²ÎÊý¶¼ÏàͬÊÇÒª´¦ÀíµÄÊý,
1.´øÓÐÁ½¸ö²ÎÊý.ÿ¶þ¸ö²ÎÊýÊÇСÊýµãµÄ×ó±ßµÚ¼¸Î»»òÓұߵڼ¸Î»,·Ö±ðÓÃÕý¸º±íʾ.×ó±ßΪ¸º,ÓÒ±ßΪ¸º.ΪËÄÉáÎåÈë.
select round(748.585929,-1) 750.000000
select round(748.58592 ......
What Is SQL?
SQL (pronounced as the letters S-Q-L or as sequel) is an abbreviation for Structured Query Language. SQL is a language designed specifically for communicating with databases.
Unlike other languages (spoken languages like English, or programming languages like Java or Visual Basic), SQ ......