sqlÖÐhaving Óëgroup byÏê½â
GROUP BY ʵÀý
±í "Sales":
Company Amount
W3Course 6500
IBM 5500
W3Course 7300
SQL:
SELECT Company, SUM(Amount) from Sales
½á¹û:
Company SUM(Amount)
W3Course 19300
IBM 19300
W3Course 19300
ÉÏÃæµÄ´úÂëÊÇÎÞЧµÄ£¬ÕâÊÇÓÉÓÚ±»·µ»ØµÄÁÐûÓнøÐв¿·ÖºÏ¼Æ¡£GROUP BY ×Ó¾äÄܽâ¾öÕâ¸öÎÊÌ⣺
SELECT Company,SUM(Amount) from Sales
GROUP BY Company
½á¹û:
Company SUM(Amount)
W3Course 13800
IBM 5500
HAVING...
°Ñ HAVING ¼ÓÈë SQL µÄÔÒòÊÇ£¬WHERE ÎÞ·¨Ó¦ÓÃÓںϼƺ¯Êý£¬¶øÈç¹ûûÓÐ HAVING£¬¾ÍÎÞ·¨²âÊÔ½á¹ûÌõ¼þ¡£
HAVING µÄÓï·¨:
SELECT column,SUM(column) from table
GROUP BY column
HAVING SUM(column) condition value
±í "Sales":
Company Amount
W3Course 6500
IBM 5500
W3Course 7300
SQL:
SELECT Company,SUM(Amount) from Sales
GROUP BY Company
HAVING SUM(Amount)>10000
½á¹û£º
Company SUM(Amount)
W3Course 13800
Ïà¹ØÎĵµ£º
²éѯÖظ´Öµ£¬ÏÔʾ³ö²»Öظ´µÄ²¿·Ö
select distinct(employeeid) from orders
²éѯÁ½¸ö±íÖ®¼äÏàͬÊý¾Ý
select orders.EmployeeID,Employees.EmployeeID
from orders INNER JOIN Employees
on Employees.EmployeeID=orders.EmployeeID
ʵÏÖÄ¿µÄ£¬Ò»¸ö±í´æ·ÅÓû§±àºÅºÍÓû§Ãû£¬ÔÚÁíÒ»¸ö±íÖпÉÒÔ¸ù¾ÝÓû§±àº ......
¡¡2005²»ÂÛÊÇÐÔÄÜ»¹ÊÇ°²È«ÐÔ¶¼ÊÇSQL SERVER2000ÓÐËùÔöÇ¿£¬ÏÖÓв»ÉÙÆóÒµÔÚʹÓÃ2005£¬»òÊÇÓеÄÓû§ÊÇ2000Óë2005ͬʱʹÓã¬ÄÇôÕâÖмä¾ÍÉæ¼°µ½Ë«Êý¾ÝµÄת»»ÎÊÌ⣺
¡¡¡¡½ñÌìÎÒÃǾÍÀ´¿´Ò»ÏÂÏà¹ØµÄʵÏÖ·½·¨£º
¡¡¡¡±¾È˵ÄʵÏÖ»·¾³½éÉÜ£º
¡¡¡¡·½·¨Ò»£º ʹÓ÷ÖÀëÓ븽¼Ó¡¡ ¸Ã·½·¨ÊʺÏÓÚ½«SQL Server 2000ÖеÄÊý¾Ýת»»µ½SQL Server2 ......
declare
@aa varchar(20)
set @aa= CONVERT(varchar(100), GETDATE(), 23)
--//ÈÕ
print @aa+' 0:00:00.000'
print @aa+' 23:59:59.999'
--//ÖÜ
print CONVERT(varchar(100), DATEADD(wk, DATEDIFF(wk,0,getdate()), 0), 23)+' 0:00:00.000'
print CONVERT(varchar(100), DATEADD(wk, DATEDIFF(wk,0,getdate( ......
µ±ÔÚÄÚÁ¬½Ó²éѯÖмÓÈëÌõ¼þʱ£¬ÎÞÂÛÊǽ«Ëü¼ÓÈëµ½join×Ӿ䣬»¹ÊǼÓÈëµ½where×Ӿ䣬ÆäЧ¹ûÊÇÍêÈ«Ò»ÑùµÄ£¬µ«¶ÔÓÚÍâÁ¬½ÓÇé¿ö¾Í²»Í¬ÁË¡£µ±°ÑÌõ¼þ¼ÓÈëµ½ join×Ó¾äʱ£¬»á·µ»ØÍâÁ¬½Ó±íµÄÈ«²¿ÐУ¬È»ºóʹÓÃÖ¸¶¨µÄÌõ¼þ·µ»ØµÚ¶þ¸ö±íµÄÐС£Èç¹û½«Ìõ¼þ·Åµ½where×Ó¾äÖУ¬½«»áÊ×ÏȽøÐÐÁ¬½Ó²Ù×÷£¬È»ºóʹÓÃwhere×Ó¾ä¶ÔÁ¬½ÓºóµÄÐнøÐÐɸѡ¡ ......