SQLÓï¾äÖеÄHaving×Ó¾äÓëwhere×Ó¾äÖ®Çø±ð
SQLÓï¾äÖеÄHaving×Ó¾äÓëwhere×Ó¾äÖ®Çø±ð
---WHERE¾ä×Ó×÷ÓÃÓÚ»ù±¾±í»òÊÔͼ£¬´ÓÖÐÑ¡ÔñÂú×ãÌõ¼þµÄÔª×é¡£HAVING×÷ÓÃÓÚ×飬´ÓÖÐÑ¡ÔñÂú×ãÌõ¼þµÄ×é---
ÔÚ˵Çø±ð֮ǰ£¬µÃÏȽéÉÜGROUP BYÕâ¸ö×Ӿ䣬¶øÔÚ˵GROUP×Ó¾äÇ°£¬ÓÖµÃÏÈ˵˵“¾ÛºÏº¯Êý”——SQLÓïÑÔÖÐÒ»ÖÖÌØÊâµÄº¯Êý¡£ÀýÈçSUM, COUNT, MAX, AVGµÈ¡£ÕâЩº¯ÊýºÍÆäËüº¯ÊýµÄ¸ù±¾Çø±ð¾ÍÊÇËüÃÇÒ»°ã×÷ÓÃÔÚ¶àÌõ¼Ç¼ÉÏ¡£
È磺
SELECT SUM(population) from vv_t_bbc ;
¡¡¡¡ÕâÀïµÄSUM×÷ÓÃÔÚËùÓзµ»Ø¼Ç¼µÄpopulation×Ö¶ÎÉÏ£¬½á¹û¾ÍÊǸòéѯֻ·µ»ØÒ»¸ö½á¹û£¬¼´ËùÓйú¼ÒµÄ×ÜÈË¿ÚÊý¡£
¡¡
¡¡¶øͨ¹ýʹÓÃGROUP BY ×Ӿ䣬¿ÉÒÔÈÃSUM ºÍ COUNT ÕâЩº¯Êý¶ÔÊôÓÚÒ»×éµÄÊý¾ÝÆð×÷Óᣵ±ÄãÖ¸¶¨ GROUP BY region
ʱ£¬Ö»ÓÐÊôÓÚͬһ¸öregion£¨µØÇø£©µÄÒ»×éÊý¾Ý²Å½«·µ»ØÒ»ÐÐÖµ£¬Ò²¾ÍÊÇ˵£¬±íÖÐËùÓгýregion£¨µØÇø£©ÍâµÄ×ֶΣ¬Ö»ÄÜͨ¹ý SUM,
COUNTµÈ¾ÛºÏº¯ÊýÔËËãºó·µ»ØÒ»¸öÖµ¡£
ÏÂÃæÔÙ˵˵“HAVING”ºÍ“WHERE”£º
¡¡¡¡HAVING×Ó¾ä¿ÉÒÔÈÃÎÒÃÇɸѡ³É×éºóµÄ¸÷×éÊý¾Ý£¬WHERE×Ó¾äÔÚ¾ÛºÏÇ°ÏÈɸѡ¼Ç¼£®Ò²¾ÍÊÇ˵×÷ÓÃÔÚGROUP BY ×Ó¾äºÍHAVING×Ó¾äÇ°£»¶ø HAVING×Ó¾äÔھۺϺó¶Ô×é¼Ç¼½øÐÐɸѡ¡£
¡¡¡¡ÈÃÎÒÃÇ»¹ÊÇͨ¹ý¾ßÌåµÄʵÀýÀ´Àí½âGROUP BY ºÍ HAVING ×Ӿ䣺
¡¡¡¡SQLʵÀý£º
¡¡¡¡Ò»¡¢ÏÔʾÿ¸öµØÇøµÄ×ÜÈË¿ÚÊýºÍ×ÜÃæ»ý£º
SELECT region, SUM(population), SUM(area)
from bbc
GROUP BY region
¡¡¡¡ÏÈÒÔregion°Ñ·µ»Ø¼Ç¼·Ö³É¶à¸ö×飬Õâ¾ÍÊÇGROUP BYµÄ×ÖÃ溬Òå¡£·ÖÍê×éºó£¬È»ºóÓþۺϺ¯Êý¶Ôÿ×éÖеIJ»Í¬×ֶΣ¨Ò»»ò¶àÌõ¼Ç¼£©×÷ÔËËã¡£
¡¡¡¡¶þ¡¢ÏÔʾÿ¸öµØÇøµÄ×ÜÈË¿ÚÊýºÍ×ÜÃæ»ý£®½öÏÔʾÄÇЩÈË¿ÚÊýÁ¿³¬¹ý1000000µÄµØÇø¡£
SELECT region, SUM(population), SUM(area)
from bbc
GROUP BY region
HAVING SUM(population)>1000000
[×¢]¡¡¡¡ÔÚÕâÀÎÒÃDz»ÄÜÓÃwhereÀ´É¸Ñ¡³¬¹ý1000000µÄµØÇø£¬ÒòΪ±íÖв»´æÔÚÕâÑùÒ»Ìõ¼Ç¼¡£
¡¡¡¡Ïà·´£¬HAVING×Ó¾ä¿ÉÒÔÈÃÎÒÃÇɸѡ³É×éºóµÄ¸÷×éÊý¾Ý,ÇÒHAVINGºóµÄÌõ¼þÈç¹ûÊǾۺϺ¯ÊýµÄÖµ,²»ÄÜÈ¡±ðÃûÀ´×÷ÅжÏ,¼´ÈçÏÂд·¨ÊDz»Äܱ»³É¹¦Ö´ÐеÄ:
SELECT region, SUM(population) as totalPopulation
, SUM(area)
from bbc
GROUP BY region
HAVING totalPopulation
>1000000
ps:Èç¹ûÏë¸ù¾ÝsumºóµÄ×ֶνøÐÐÅÅÐò¿ÉÒÔÔÚºóÃæ¼ÓÉÏ£ºorder by sum(population) desc/asc
WHERE¾ä×Ó×÷ÓÃÓÚ»ù±¾±í»òÊÔͼ£¬´ÓÖÐÑ¡ÔñÂú×ãÌõ¼þµÄÔª×é¡£HAVIN
Ïà¹ØÎĵµ£º
¹ØÓÚͨ¹ýÍâÍøÁ¬½ÓÄÚÍøSQL Server·þÎñÆ÷µÄ·½·¨
½üÈÕ£¬ÔÚÂÛ̳ÉÏ¿´µ½µÄÓйØSQL ServerÔ¶³ÌÁ¬½ÓµÄÎÄÕºó£¬ÕæµÄÊÜÒæÁ¼¶à¡£Ò»Ö±ÒÔÀ´À§»óÁËÒѾõĹØÓÚ´ÓÍâÍøÈçºÎÁ¬½Óµ½ÄÚÍøµÄSQL Server·þÎñÆ÷Éϵķ½·¨£¬ÏÖÒѽâ¾öÁË£¬ÏàÐÅÒ²Óв»ÉÙÏóÎÒÕâÑùµÄºüÓÑÃÇ¡£¹Ê´Ë£¬ÏÖÔÚ°ÑÎÒʵÏֵķ½·¨Ä ......
SQL*PLUSµÄʹÓÃ
1¡¢Í˳öSQL*PLUS
EXIT£ºÖÕÖ¹SQL*PLUS£¬²¢½«¿ØÖÆȨ½»»¹¸øOS¡£
2¡¢±à¼ÃüÁî
1) List»òL£ºÏÔʾ»º³åÇøÖÐËùÓеÄÐС£
SQL> list
1 select
2 *
3* from dept --×¢£º*´ú±íµ±Ç°ÐС£ ......
Ò»).´´½¨²¿ÊðÏîÄ¿
1. ÔÚ“Îļþ”²Ëµ¥ÉÏÖ¸Ïò“Ìí¼ÓÏîÄ¿”£¬È»ºóÑ¡Ôñ“н¨ÏîÄ¿”¡£
2. ÔÚ“Ìí¼ÓÐÂÏîÄ¿”¶Ô»°¿òÖУ¬Ñ¡Ôñ“ÏîÄ¿ÀàÐÍ”´°¸ñÖеē°²×°ºÍ²¿ÊðÏîÄ¿”£¬È»ºóÑ¡Ôñ“Ä£°å”´°¸ñ ......
1¡¢²éÕÒ±íÖжàÓàµÄÖظ´¼Ç¼£¬Öظ´¼Ç¼ÊǸù¾Ýµ¥¸ö×ֶΣ¨peopleId£©À´ÅжÏ
select * from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)
2¡¢É¾³ý±íÖжàÓàµÄÖظ´¼Ç¼£¬Öظ´¼Ç¼ÊǸù¾Ýµ¥¸ö× ......
²Î¼û¡¶SQL Sever 2005 Êý¾Ý¿â»ù´¡¼°Ó¦Óü¼Êõ½Ì³ÌÓëʵѵ¡· ÖÜÆæ
SQL ServerÖÐÓÐÎåÖÖÔ¼ÊøÀàÐÍ£¬·Ö±ðÊÇCHECKÔ¼Êø¡¢DEFAULTÔ¼Êø¡¢PRIMARY KEYÔ¼Êø¡¢FOREIGN KEYÔ¼ÊøºÍUNIQUEÔ¼Êø¡£
1. CHECKÔ¼Êø£º
CHECKÔ¼ÊøÓÃÓÚÏÞÖÆÊäÈëÒ»Áлò¶àÁеÄÖµµÄ·¶Î§£¬Í¨¹ýÂß¼±í´ïʽÀ´ÅжÏÊý¾ ......