Ò׽ؽØͼÈí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

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


Ïà¹ØÎĵµ£º

EMS SQL Manager for Mysql°ïÁËÎÒµÄ棬²¿·ÖÁеIJÙ×÷

 
ÓÉÓÚµ±³õ±íÉè¼ÆµÄ²»ºÏÀí£¬ÂýÂýµÄ·¢ÏÖÀË·ÑÐí¶à¿Õ¼ä£¬ÇÒ¶ÔÀ©Õ¹²»Àû¡£¾ö¶¨²»°ÑͬÀàÐÍÄÚÈݲ¢ÅÅÁб£´æ£¬ËùÒÔ½ñÌì°Ñ¶àÓàµÄÁмôµô£¬²¹ÔÚ±£ÁôµÄÁÐÏÂÃæ¡£
 
 
 
ÔÚ±íÃûµãÓÒ¼ü£¬Êý¾Ý²Ù×÷£¬µ¼³öSQL½Å±¾Êý¾Ý¡£ÕÕÌáʾ²Ù×÷£¬ÕâÀïÎÒϲ»¶µÄÊÇ¿ÉÒÔÖ¸¶¨Áе¼³ö¡£
 
È»ºó°Ñ²»ÐèÒªµÄ¿ÕÁÐɾµô£¬¸ÄһϲåÈëµÄÁÐ ......

²»ÊÇÆ´SQLÓï¾äµÄÒ³Ãæ´æ´¢¹ý³Ì

ALTER PROCEDURE [dbo].[GetUsersList]
@BigClassID int=0,--´óÀà±ðID
@SmallClassID int=0 ,--СÀà±ðID
@pageindex int=1,
@pagesize int=3,
@ret int output --¹²¼¸Ìõ
AS
set rowcount @pagesize
if @SmallClassID>0
begin
select * from(
select id,BigClassName,SmallClassName,UserName,Corporation,r ......

SQL SERVER 2000ÖзÃÎÊOracleÊý¾Ý¿â·þÎñÆ÷µÄ¼¸ÖÖ·½·¨

ÔÚ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µÄÇý¶¯£¬
×¢Òâ:Óû§ÃûºÍ±íÃûÒ»¶¨Òª´óС£¬·þÎñÆ÷ºÍ ......

[SQLɾ³ý] SQLÓï¾äÖÐdelete, drop, truncate±È½Ï


DELETE from SCOTT.EMP;
DROP from SCOTT.EMP;
TRUNCATE from EMP;
Ïàͬµã 
truncateºÍ²»´øwhere×Ó¾äµÄdelete, ÒÔ¼°drop¶¼»áɾ³ý±íÄÚµÄÊý¾Ý 
²»Í¬µã: 
1. truncateºÍ deleteֻɾ³ýÊý¾Ý²»É¾³ý±íµÄ½á¹¹(¶¨Òå) 
    dropÓï¾ä½«É¾³ý±íµÄ½á¹¹±»ÒÀÀµµÄÔ¼Êø(constrain),´¥·¢Æ÷(trigge ......

SQLÖظ´¼Ç¼²éѯ

1¡¢²éÕÒ±íÖжàÓàµÄÖظ´¼Ç¼£¬Öظ´¼Ç¼ÊǸù¾Ýµ¥¸ö×ֶΣ¨peopleId£©À´ÅжÏ
select * from people
where peopleId in (select   peopleId  from   people  group  by   peopleId  having  count(peopleId) > 1)
2¡¢É¾³ý±íÖжàÓàµÄÖظ´¼Ç¼£¬Öظ´¼Ç¼ÊǸù¾Ýµ¥¸ö× ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØͼ | ¸ÓICP±¸09004571ºÅ