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Ìõ¼Ç¼£¨±í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 ......
½ñÌìÔÚÏîÄ¿ÖÐÓÐÒ»ÎÊÌ⣬ÔÚÍøÉϲéѯÁËcaseµÄÓ÷¨£¬Ìû³öÀ´ºÍ´ó¼Ò·ÖÏíÏ¡£
ÎÊÌâÃèÊö£ºÔÚÒ»ÕűíÖÐÓÐÒ»×Ö¶ÎbitÀàÐÍ£¬±íʾ´ËÌõÊý¾ÝÊÇ·ñ±»Ëø¶¨£¬ÔÚÒ³ÃæÉÏÓÐÒ»°´Å¥ÊǶԴËÌõÊý¾Ý½øÐÐËø¶¨ºÍ½âËøµÄ£¬Ñ¡ÔñÒ³ÃæÖеÄÊý¾Ý£¬µã»÷Õâ¸ö°´Å¥£¬Èç¹ûÕâÌõÊý¾ÝÊÇËø¶¨µÄ£¬¾Í½âËø£»Èç¹ûÊÇδ˵¶¨µÄ¾ÍËø¶¨£¬ÕâÑù¾ÍÓÃÒ»ÌõÓï¾äÀ´ÊµÏÖ¡£ºóÀ´Ï ......
ÔÚÁгö±íÖÐËùÓÐ×Ö¶ÎÃûµÄʱºò£¬Óõ½ÁËÕâÑùÒ»¸öSQLº¯Êý£ºobject_id
ÕâÀïÎÒ½«Æä×÷ÓÃÓëÓ÷¨ÁгöÀ´£¬ºÃÈôó¼ÒÃ÷°×£º
OBJECT_ID£º
·µ»ØÊý¾Ý¿â¶ÔÏó±êʶºÅ¡£
Óï·¨
OBJECT_ID ( 'object' )
²ÎÊý
'object'
ҪʹÓõĶÔÏó¡£object µÄÊý¾ÝÀàÐÍΪ char »ò nchar¡£Èç¹û object µÄÊý¾ÝÀàÐÍÊÇ char£¬ÄÇôÒþÐÔ½«Æäת»»³É ncha ......
--»ùÓÚʱ¼äSQLº¯Êý--
getdate() --·µ»Øµ±Ç°ÏµÍ³ÈÕÆÚºÍʱ¼ä¡£
DateAdd --ÔÚÏòÖ¸¶¨ÈÕÆÚ¼ÓÉÏÒ»¶Îʱ¼äµÄ»ù´¡ÉÏ£¬·µ»ØÐ嵀 datetime Öµ¡£
DATEADD ( datepart , number, date )
--Àý£ºÏòµ±ÌìµÄʱ¼äÔö¼Ó5Ìì
select dateadd(dd,5,getdate())
datediff --·µ»Ø¿çÁ½¸öÖ¸¶¨ÈÕÆÚµÄÈÕÆÚºÍʱ¼ä±ß½çÊý¡£]
---ÀýÈç
& ......