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

sql 2005 ·ÖÒ³£¬ÅÅÃûº¯Êý

 WITH V AS(SELECT ROW_NUMBER() OVER(order by fcustid desc) AS RN,*  from  ts_dict AS SourceView)
SELECT * from V WHERE RN BETWEEN 1 AND 10
×ªÔØµØÖ·£ºhttp://www.cnblogs.com/nokiaguy/archive/2009/02/05/1384860.html
ÅÅÃûº¯ÊýÊÇSQL Server2005мӵŦÄÜ¡£ÔÚSQL Server2005ÖÐÓÐÈçÏÂËĸöÅÅÃûº¯Êý£º
1. row_number
2. rank
3. dense_rank
4. ntile   
    ÏÂÃæ·Ö±ð½éÉÜÒ»ÏÂÕâËĸöÅÅÃûº¯ÊýµÄ¹¦Äܼ°Ó÷¨¡£ÔÚ½éÉÜ֮ǰ¼ÙÉèÓÐÒ»¸öt_table±í£¬±í½á¹¹Óë±íÖеÄÊý¾ÝÈçͼ1Ëùʾ£º
ͼ1
ÆäÖÐfield1×ֶεÄÀàÐÍÊÇint£¬field2×ֶεÄÀàÐÍÊÇvarchar
Ò»¡¢row_number
    row_numberº¯ÊýµÄÓÃ;ÊǷdz£¹ã·º£¬Õâ¸öº¯ÊýµÄ¹¦ÄÜÊÇΪ²éѯ³öÀ´µÄÿһÐмǼÉú³ÉÒ»¸öÐòºÅ¡£row_numberº¯ÊýµÄÓ÷¨ÈçÏÂÃæµÄSQLÓï¾äËùʾ£º
select row_number() over(order by field1) as row_number,* from t_table
    ÉÏÃæµÄSQLÓï¾äµÄ²éѯ½á¹ûÈçͼ2Ëùʾ¡£
ͼ2
    ÆäÖÐrow_numberÁÐÊÇÓÉrow_numberº¯ÊýÉú³ÉµÄÐòºÅÁС£ÔÚʹÓÃrow_numberº¯ÊýÊÇҪʹÓÃover×Ó¾äÑ¡Ôñ¶ÔijһÁнøÐÐÅÅÐò£¬È»ºó²ÅÄÜÉú³ÉÐòºÅ¡£
    ʵ¼ÊÉÏ£¬row_numberº¯ÊýÉú³ÉÐòºÅµÄ»ù±¾Ô­ÀíÊÇÏÈʹÓÃover×Ó¾äÖеÄÅÅÐòÓï¾ä¶Ô¼Ç¼½øÐÐÅÅÐò£¬È»ºó°´×ÅÕâ¸ö˳ÐòÉú³ÉÐòºÅ¡£over×Ó¾äÖеÄorder by×Ó¾äÓëSQLÓï¾äÖеÄorder by×Ó¾äûÓÐÈκιØÏµ£¬ÕâÁ½´¦µÄorder by ¿ÉÒÔÍêÈ«²»Í¬£¬ÈçÏÂÃæµÄSQLÓï¾äËùʾ£º
select row_number() over(order by field2 desc) as row_number,* from t_table order by field1 desc
    ÉÏÃæµÄSQLÓï¾äµÄ²éѯ½á¹ûÈçͼ3Ëùʾ¡£
ͼ3
    ÎÒÃÇ¿ÉÒÔʹÓÃrow_numberº¯ÊýÀ´ÊµÏÖ²éѯ±íÖÐÖ¸¶¨·¶Î§µÄ¼Ç¼£¬Ò»°ã½«ÆäÓ¦Óõ½WebÓ¦ÓóÌÐòµÄ·ÖÒ³¹¦ÄÜÉÏ¡£ÏÂÃæµÄSQLÓï¾ä¿ÉÒÔ²éѯt_table±íÖеÚ2ÌõºÍµÚ3Ìõ¼Ç¼£º
with t_rowtable
as
(
    select row_number() over(order by field1) as row_number,* from t_table
)
select * from t_rowtable where row_number>1 and row_number < 4 order by f


Ïà¹ØÎĵµ£º

sqlÓï¾ä£¬È¡³ö±íAÖеĵÚ31Ìõµ½40Ìõ¼Ç¼

 sqlÓï¾ä£¬È¡³ö±íAÖеĵÚ31Ìõµ½40Ìõ¼Ç¼£¨±íAÒÔ×Ô¶¯Ôö³¤µÄID×öÖ÷¼ü£¬×¢ÒâID¿ÉÄÜÊDz»Á¬ÐøµÄ£©
-->select top 10 * from a where id not in (select top 30 id from a order by id) order by id
    ²éѯǰʮÌõ¼Ç¼£¬µ«Ìõ¼þÊÇ£ºID²»ÔÚǰÈýÊ®ÌõµÄIDÀïÃæ
-->select top 10 * from (select top 40 ......

SQLÖÐCASEʹÓÃ

 ½ñÌìÔÚÏîÄ¿ÖÐÓÐÒ»ÎÊÌ⣬ÔÚÍøÉϲéѯÁËcaseµÄÓ÷¨£¬Ìû³öÀ´ºÍ´ó¼Ò·ÖÏíÏ¡£
 ÎÊÌâÃèÊö£ºÔÚÒ»ÕűíÖÐÓÐÒ»×Ö¶ÎbitÀàÐÍ£¬±íʾ´ËÌõÊý¾ÝÊÇ·ñ±»Ëø¶¨£¬ÔÚÒ³ÃæÉÏÓÐÒ»°´Å¥ÊǶԴËÌõÊý¾Ý½øÐÐËø¶¨ºÍ½âËøµÄ£¬Ñ¡ÔñÒ³ÃæÖеÄÊý¾Ý£¬µã»÷Õâ¸ö°´Å¥£¬Èç¹ûÕâÌõÊý¾ÝÊÇËø¶¨µÄ£¬¾Í½âËø£»Èç¹ûÊÇδ˵¶¨µÄ¾ÍËø¶¨£¬ÕâÑù¾ÍÓÃÒ»ÌõÓï¾äÀ´ÊµÏÖ¡£ºóÀ´Ï ......

sql ÖÐOBJECT_ID º¯ÊýµÄ½âÊÍ

ÔÚÁгö±íÖÐËùÓÐ×Ö¶ÎÃûµÄʱºò£¬Óõ½ÁËÕâÑùÒ»¸öSQLº¯Êý£ºobject_id
ÕâÀïÎÒ½«Æä×÷ÓÃÓëÓ÷¨ÁгöÀ´£¬ºÃÈôó¼ÒÃ÷°×£º
OBJECT_ID£º
·µ»ØÊý¾Ý¿â¶ÔÏó±êʶºÅ¡£
Óï·¨
OBJECT_ID ( 'object' )
²ÎÊý
'object'
ҪʹÓõĶÔÏó¡£object µÄÊý¾ÝÀàÐÍΪ char »ò nchar¡£Èç¹û object µÄÊý¾ÝÀàÐÍÊÇ char£¬ÄÇôÒþÐÔ½«Æäת»»³É ncha ......

SQLÈÕÆÚºÍʱ¼äº¯Êý

 
--»ùÓÚʱ¼äSQLº¯Êý--
getdate() --·µ»Øµ±Ç°ÏµÍ³ÈÕÆÚºÍʱ¼ä¡£
DateAdd --ÔÚÏòÖ¸¶¨ÈÕÆÚ¼ÓÉÏÒ»¶Îʱ¼äµÄ»ù´¡ÉÏ£¬·µ»ØÐ嵀 datetime Öµ¡£
DATEADD ( datepart , number, date )
--Àý£ºÏòµ±ÌìµÄʱ¼äÔö¼Ó5Ìì
select dateadd(dd,5,getdate())
datediff --·µ»Ø¿çÁ½¸öÖ¸¶¨ÈÕÆÚµÄÈÕÆÚºÍʱ¼ä±ß½çÊý¡£]
---ÀýÈç
 & ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ