sql group by Óë havingµÄÓ÷¨
1. GROUP BY ÊÇ·Ö×é²éѯ, Ò»°ã GROUP BY ÊǺ;ۺϺ¯ÊýÅäºÏʹÓÃ
group by ÓÐÒ»¸öÔÔò,¾ÍÊÇ select ºóÃæµÄËùÓÐÁÐÖÐ,ûÓÐʹÓþۺϺ¯ÊýµÄÁÐ,±ØÐë³öÏÖÔÚ group by ºóÃ棨ÖØÒª£©
ÀýÈç,ÓÐÈçÏÂÊý¾Ý¿â±í£º
A B
1 abc
1 bcd
1 asdfg
Èç¹ûÓÐÈçϲéѯÓï¾ä£¨¸ÃÓï¾äÊÇ´íÎóµÄ£¬ÔÒò¼ûÇ°ÃæµÄÔÔò£©
select A,B from table group by A
¸Ã²éѯÓï¾äµÄÒâͼÊÇÏëµÃµ½ÈçϽá¹û(µ±È»Ö»ÊÇÒ»ÏàÇéÔ¸)
A B
abc
1 bcd
asdfg
ÓÒ±ß3ÌõÈçºÎ±ä³ÉÒ»Ìõ,ËùÒÔÐèÒªÓõ½¾ÛºÏº¯Êý£¬ÈçÏÂ(ÏÂÃæÊÇÕýÈ·µÄд·¨):
select A,count(B) as ÊýÁ¿ from table group by A
ÕâÑùµÄ½á¹û¾ÍÊÇ
A ÊýÁ¿
1 3
2. Having
where ×Ó¾äµÄ×÷ÓÃÊÇÔÚ¶Ô²éѯ½á¹û½øÐзÖ×éÇ°£¬½«²»·ûºÏwhereÌõ¼þµÄÐÐÈ¥µô£¬¼´ÔÚ·Ö×é֮ǰ¹ýÂËÊý¾Ý£¬Ìõ¼þÖв»ÄÜ°üº¬¾Û×麯Êý£¬Ê¹ÓÃwhereÌõ¼þÏÔʾÌض¨µÄÐС£
having ×Ó¾äµÄ×÷ÓÃÊÇɸѡÂú×ãÌõ¼þµÄ×飬¼´ÔÚ·Ö×éÖ®ºó¹ýÂËÊý¾Ý£¬Ìõ¼þÖо³£°üº¬¾Û×麯Êý£¬Ê¹ÓÃhaving Ìõ¼þÏÔʾÌض¨µÄ×飬Ҳ¿ÉÒÔʹÓöà¸ö·Ö×é±ê×¼½øÐзÖ×é¡£
having ×Ӿ䱻ÏÞÖÆ×ÓÒѾÔÚSELECTÓï¾äÖж¨ÒåµÄÁк;ۺϱí´ïʽÉÏ¡£Í¨³££¬ÄãÐèҪͨ¹ýÔÚHAVING×Ó¾äÖÐÖظ´¾ÛºÏº¯Êý±í´ïʽÀ´ÒýÓþۺÏÖµ£¬¾ÍÈçÄãÔÚSELECTÓï¾äÖÐ×öµÄÄÇÑù¡£ÀýÈ磺
SELECT A COUNT(B) from TABLE GROUP BY A HAVING COUNT(B)>2
Àý×Óeg£º
ÎÊÌ⣺
Óбí¸ñÈçÏ£º
create table workers(id int primary key,name varchar(12),department varchar(12),salary int,releaseDay date);
Ϊ
id ±àÂë
name ÐÕÃû
department ²¿ÃÅ
salary ¹¤×Ê
releaseDay ·¢·ÅÈÕÆÚ ¸ñʽ 2009-10-10
ÏÖÔÚÒªÇóд³ösqlÓï¾ä£º
ÕÒ³öÔø¾ÔÚÈκÎÒ»¸öÔ·¢ÁËÁ½´Î»òÕßÁ½´Î¹¤×ÊÒÔÉϵÄÔ±¹¤ÐÅÏ¢£¬µ±Ô¹¤×Ê·¢·Å´ÎÊý£¬¹¤×Ê×ÜÊý£¬·¢·ÅÔ·ݣ»
½â´ð£º
create table ty_workers
(
id int primary key,
name varchar(12),
department varchar(12),
salary int,
releaseDay date
);
select * from ty_workers
select name ÐÕÃû, count(salary) ·¢·Å´ÎÊý , sum(salary) ·¢·Å×ܶî, substr(to_char(releaseDay,'yyyy-mm-dd'),0,7) ·¢·ÅÔ·Ý, department ²¿ÃÅ
from ty_workers
gr
Ïà¹ØÎĵµ£º
²¢¼¯:
ʹÓà UNION ÔËËã·û×éºÏ¶à¸ö½á¹û
SELECT name,num from Table1
UNION
SELECT name,num from Table2
×¢Ò⣺²éѯµÄ×ֶθöÊý±ØÐëÏàͬ£¬Table2µÄ×Ö¶ÎÀàÐÍÒª¸úTable1µÄÏàͬ.
Èç¹ûʹÓà UNION ÔËËã·û£¬ÄÇôµ¥¶ÀµÄ SELECT Óï¾ä²»ÄÜ°üº¬Æä×Ô¼ºµÄ ORDER BY »ò COMPUTE ×Ӿ䡣ֻÄÜÔÚ×îºóÒ»¸ö SELECT Óï¾äµÄºóÃæʹÓÃÒ»¸ö ORD ......
Ò»¡£SQL Server µÄÈýÖÖ×Ô¶¨Ò庯Êý
“×Ô¶¨Ò庯Êý”ÊÇÎÒÃÇƽ³£µÄ˵·¨£¬¶ø“Óû§¶¨ÒåµÄº¯Êý”ÊÇ SQL Server ÖÐÊéÃæµÄ˵·¨¡£
SQL Server 2000 ÔÊÐíÓû§´´½¨×Ô¶¨Ò庯Êý£¬×Ô¶¨Ò庯Êý¿ÉÒÔÓзµ»ØÖµ¡£
×Ô¶¨Ò庯Êý·ÖΪ£º±êÁ¿Öµº¯Êý»ò±íÖµº¯Êý
Èç¹û RETURNS ×Ó¾äÖ¸¶¨Ò»ÖÖ±êÁ¿Êý¾ÝÀàÐÍ£¬Ôòº¯ÊýΪ±êÁ¿Öµº¯Êý¡£¿É ......
weblogic8.1ûÓдøSQL Server 2005µÄJDBCÇý¶¯£¬ÐèҪȥ΢ÈíÍøÕ¾ÏÂÔØ¡£
µØÖ·£ºhttp://msdn.microsoft.com/vstudio/express/sql/download/
Æô¶¯ SQL Server Configuration manager£¬Õ¹¿ªSQL Server 2005
ÍøÂçÅäÖ㬵¥»÷“MSSQLSERVERµÄÐÒé&rdqu ......
Èç¹ûÔÚSQL Server ÀïÐèÒª¶¨Ê±»òÕßÿ¸ôÒ»¶Îʱ¼äÖ´ÐÐij¸ö´æ´¢¹ý³Ì»ò3200×Ö·ûÒÔÄÚµÄSQLÓï¾äʱ, ¿ÉÒÔÓùÜÀí->SQL Server´úÀí->×÷ÒµÀ´ÊµÏÖ¡£
¡¡¡¡1¡¢¹ÜÀí->SQL Server´úÀí->×÷Òµ(°´Êó±êÓÒ¼ü)->н¨×÷Òµ
¡¡¡¡2¡¢Ð½¨×÷ÒµÊôÐÔ(³£¹æ)->Ãû³Æ[×Ô¶¨Òå±¾´Î×÷ÒµµÄÃû³Æ]->ÆôÓõķ½¿òÄÚÊǹ´ºÅ->
·ÖÀà´¦¿ ......
´´½¨´æ´¢¹ý³Ì֮ǰҪÏÈÓÃuseÓï¾äÉùÃ÷Òª½«´æ´¢¹ý³Ì´æ´¢ÔÚÄĸöÊý¾Ý¿âÖÐ. e.g use company; Ö´ÐÐÖ®.Ö®ºó¾Í¿ÉÒÔÉùÃ÷´æ´¢¹ý³ÌÁË. e.g create procedure *** @id varchar(50) , @name int output as select @name=name from table where id=@id È»ºó¿ÉÒÔÓÃalter¶Ô´æ´¢¹ý³Ì½øÐÐÐÞ¸Ä. declare¿ÉÒÔÉùÃ÷Ò»¸ö±äÁ¿ ÓÃset¶Ô±ä ......