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
Ïà¹ØÎĵµ£º
1 windowsµÇ¼ÕË»§¿Ú£ºEXEC ap_grantlogin 'windowsÓòÃû\ÓòÕË»§'
2 SQL µÇ¼ÕË»§:EXEC sp_addlogin 'ÕË»§Ãû','ÃÜÂë'
3 ´´½¨Êý¾Ý¿âÓû§:exec spgrantdbaccess 'µÇ¼ÕË»§','Êý¾Ý¿âÓû§'
¶þ ¸øÊý¾Ý¿âÓû§ÊÚȨ
grant ȨÏÞ on ±íÃû to Êý¾Ý¿âÓû§ ......
SQLÖÐCONVERTº¯Êý×î³£ÓõÄÊÇʹÓÃconvertת»¯³¤ÈÕÆÚΪ¶ÌÈÕÆÚ
Èç¹ûֻҪȡyyyy-mm-dd¸ñʽʱ¼ä, ¾Í¿ÉÒÔÓà convert(nvarchar(10),field,120)
120 ÊǸñʽ´úÂë, nvarchar(10) ÊÇָȡ³öǰ10λ×Ö·û.
SELECT CONVERT(nvarchar(10), getdate(), 120)
SELECT CONVERT(varchar(10), getdate(), 120)
SELECT CONVERT(char(10), ge ......
»ùÓÚSQL Server ·ÖÒ³´æ´¢¹ý³ÌµÄÑݽø ÊÕ²Ø
Õë¶ÔÊý¾Ý¿âÊý¾ÝÔÚUI½çÃæÉϵķÖÒ³ÊÇÀÏÉú³£Ì¸µÄÎÊÌâÁË£¬ÍøÉϺÜÈÝÒ×ÕÒµ½¸÷Ö֓ͨÓô洢¹ý³Ì”´úÂ룬¶øÇÒÓÐЩ»¹¶¨ÖƲéѯÌõ¼þ£¬¿´ÉÏȥʹÓúܷ½±ã¡£±ÊÕß´òËãͨ¹ý±¾ÎÄÒ²À´¼òµ¥Ì¸Ò»Ï»ùÓÚSQL SERVER 2000µÄ·ÖÒ³´æ´¢¹ý³Ì£¬Í¬Ê±Ì¸Ì¸SQL SERVER 2005Ï·ÖÒ³´æ´¢¹ý³ÌµÄÑ ......
1¡¢Î½´Ê ν´ÊÔÊÐíÄú¹¹ÔìÌõ¼þ£¬ÒÔ±ãÖ»´¦ÀíÂú×ãÕâЩÌõ¼þµÄÄÇЩÐС£
2¡¢Ê¹Óà IN ν´Ê
ʹÓà IN ν´Ê½«Ò»¸öÖµÓëÆäËû¼¸¸öÖµ½øÐбȽϡ£ÀýÈ磺
SELECT NAME from STAFF WHERE DEPT IN (20, 15)
´ËʾÀýÏ൱ÓÚ£º
SELECT NAME from STAFF WHERE DEPT = 20 OR DEPT = 15
µ±×Ó²éѯ·µ»ØÒ»×éֵʱ£¬¿ÉʹÓà IN ºÍ NOT IN ÔËËã·û¡£ÀýÈç£ ......
COLLATE ÊÇÒ»¸ö×Ӿ䣬¿ÉÓ¦ÓÃÓÚÊý¾Ý¿â¶¨Òå»òÁж¨ÒåÒÔ¶¨ÒåÅÅÐò¹æÔò£¬»òÓ¦ÓÃÓÚ×Ö·û´®±í´ïʽÒÔÓ¦ÓÃÅÅÐò¹æÔòת»»¡£
Óï·¨
COLLATE { <collation_name> | database_default }
<collation_name> :: =
{ Windows_collation_name } | { SQL_collation_name }
²ÎÊý
collation_name
  ......