sql DATEPART£¨£©º¯Êý
cdateÊÇdatetimeÀàÐ͵Ä×Ö¶Î
ͳ¼ÆÒ»ÄêµÄÈçÏÂ
select datepart(yy,cdate) as 'Ô·Ý',sum(cmoney) from consumption group by datepart(yy,cdate)
ͳ¼ÆÒ»ÔµÄÈçÏÂ
select datepart(mm,cdate) as 'Ô·Ý',sum(cmoney) from consumption where datepart(yy,cdate)=2009 group by datepart(mm,cdate)
ͳ¼ÆÒ»ÖܵÄÈçÏÂ
select DATEPART(wk,cdate) as 'ÖÜ', sum(cmoney) from consumption group by DATEPART(wk,cdate)
ͳ¼ÆÒ»ÌìµÄÈçÏÂ
select DATEPART(dd,cdate) as 'ºÅ', sum(cmoney) from consumption where datepart(mm,cdate)=6 group by DATEPART(dd,cdate)
ÒÔÉÏÓõ½µÄ¾ÍÊÇ“DATEPART£¨£©”º¯Êý¡£º¯ÊýDATEPART£¨£©µÄ²ÎÊýÊÇÁ½¸ö±äÁ¿¡£µÚÒ»¸ö±äÁ¿Ö¸¶¨Òª³éÈ¡ÈÕÆÚµÄÄÄÒ»²¿·Ö£»µÚ¶þ¸ö±äÁ¿ÊÇʵ¼ÊµÄÊý¾Ý¡£
ÈÕÆÚµÄ¸÷²¿·Ö¼°Æä¼òд
ÈÕÆÚ²¿·Ö ¼òд Öµ
year yy 1753--9999
quarter qq 1--4
month mm 1--12
day of year dy 1--366
day dd 1--31
week wk &n
Ïà¹ØÎĵµ£º
declare @i int
set @i=1
while @i<30
begin
insert into test (userid) values(@i)
set @i=@i+1
end
---------------
while Ìõ¼þ
begin
Ö´ÐвÙ×÷
set @i=@i+1
end
WHILE
ÉèÖÃÖØ¸´Ö´ÐÐ SQL Óï¾ä»òÓï¾ä¿éµÄÌõ¼þ¡£Ö»ÒªÖ¸¶¨µÄÌõ¼þÎªÕæ£¬¾ÍÖØ¸´Ö´ÐÐÓï¾ä¡£¿ÉÒÔʹÓà BREAK ºÍ CONTINUE ¹Ø¼ü×ÖÔÚÑ»·ÄÚ²¿¿ ......
ÀýÈç²éÕÒ¹ØÓÚ¶Ôlibrary ....µÈ´ýʼþÓй±Ï×µÄSQL
select sql_text from V$sqlarea where (address,hash_value) in
(select sql_address,sql_hash_value from v$session where event like 'library%');
´ËÓï¾äÖ»ÄÜÔËÐÐÓÚ10g°æ±¾ÒÔÉÏ£¬ÒòΪ10gÖÐv$sessionÊÓͼ°üº¬Á˵ȴýʼþµÄÐÅÏ¢ÁË£¬9iÖÐûÓÐ ......
/*ͳ¼ÆÃ¿ÌìÊý¾Ý×ÜÁ¿ÈýÖÖ·½·¨£º
select convert(char(10),happentime ,120) as date ,count(1) from table1
group by convert(char(10),happentime ,120) order by date desc
s ......
SQL Server 2005Òý½øÁËÒ»¸öºÜÓмÛÖµµÄеÄTransact-SQLÓïÑÔ×é¼þ£ºÒ»¸öͨÓñí±í´ïʽ£¨Common Table Expression£¬CTE£©£¬ËüÊÇÅÉÉú±íºÍÊÓͼµÄÒ»¸ö±ã½ÝµÄÌæ´ú¡£Í¨¹ýʹÓÃCTE£¬ÎÒÃÇ¿ÉÒÔ´´½¨Ò»¸öÃüÃû½á¹û¼¯À´ÔÚSELECT¡¢INSERT¡¢UPDATEºÍDELETEÓï¾äÖÐÒýÓ㬶øÎÞÐë±£´æ½á¹û¼¯½á¹¹µÄÈκÎÔªÊý¾Ý¡£ÔÚ±¾ÎÄÖУ¬ÎÒ½«²ûÊöÈçºÎÔÚSQL Server 2 ......