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
Ïà¹ØÎĵµ£º
Ò»¡¢Orcale ʱµÄ²éѯ
String hql = "from SmsTemplate t where 1=1 ";
if (model != null && !"".equals(model.getEndTimes())&& null!=model.getEndTimes() ) {
SimpleDateFormat dateFm = new SimpleDateFormat("yyyy-MM-dd"); //¸ñʽ»¯µ±Ç°ÏµÍ³ÈÕÆÚ
Date ......
ÔÚSQL SERVER 20000ÖзÃÎÊOracleÊý¾Ý¿â·þÎñÆ÷µÄ¼¸ÖÖ·½·¨
1.ͨ¹ýÐм¯º¯Êýopendatasource
ÒªÇó:±¾µØ°²×°Oracle¿Í»§¶Ë
select * from opendatasource('MSDAORA', 'Data Source=XST4;User ID=manager;Password=sjpsjsjs')..MISD.PBCATCOL
ÆäÖУ¬MSDAORAÊÇOLEDB FOR OracleµÄÇý¶¯£¬
×¢Òâ:Óû§ÃûºÍ±íÃûÒ»¶¨Òª´óС£¬·þÎñÆ÷ºÍ ......
¿ª·¢»·¾³£º
windows2003£¬°²×°ÁËvs2008¡¢SQL Server2005£¬ÕâÊÇ»ù´¡£¨µ±È»ÓÃvs2003£¬vs2005£¬SQL Server2000ɶµÄÒ²¿ÉÒÔ£¬²»¹ýеÄ×ÜÊǹ¦ÄÜÇ¿´óЩô£¬ºÇºÇ£©£»
ÒòΪ¿ª·¢Ê±¾ø´ó²¿·Öʱ¼äÊÇÔÚÄ£ÄâÆ÷Éϵ÷ÊÔ£¬¶ø²»ÊÇPPC£¬ËùÒÔÄ£ÄâÆ÷ÊDZØÐë°²×°µÄ¡£Õë¶ÔÄãµÄPPC²Ù×÷ϵͳѡÔñ¶ÔÓ¦µÄÄ£ÄâÆ÷Óësdk°ü£¬ÎÒ°²×°µÄÊÇWM6ÖÐÎÄ°æµÄ¡££¨windows ......
1¡¢²éÕÒ±íÖжàÓàµÄÖظ´¼Ç¼£¬Öظ´¼Ç¼ÊǸù¾Ýµ¥¸ö×ֶΣ¨peopleId£©À´ÅжÏ
select * from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)
2¡¢É¾³ý±íÖжàÓàµÄÖظ´¼Ç¼£¬Öظ´¼Ç¼ÊǸù¾Ýµ¥¸ö× ......
ʹÓÃPowerDesignerÉú³ÉÊý¾Ý¿â
½¨±íSQL
½Å
±¾Ê±£¬ÓÈÆäÊÇOracleÊý¾Ý¿âʱ£¬±íÃûÒ»°ã»á´øÒýºÅ¡£Æäʵ¼ÓÒýºÅÊÇPL/SQLµÄ¹æ·¶£¬Êý¾Ý¿â»áÑϸñ°´ÕÕ“”ÖеÄÃû³Æ½¨±í£¬Èç¹ûûÓГ”£¬»á°´ÕÕ
ORACLEĬÈϵÄÉèÖý¨±í£¨DBA
STUDIOÀïÃ棩£¬Ä¬ÈÏÊÇÈ«²¿´óд£¬ÕâÑù£¬ÔÚORACLEÊý¾Ý¿âÀïµÄ×ֶξÍÈç“Column_1&rdqu ......