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 Êý¾Ý¿âÓû§ ......
http://hi.baidu.com/dumao/blog/item/1cafa71e5886d019413417e4.html
1.È«ÎÄË÷Òý¸ÅÊö
¶Ô Microsoft® SQL Server™ 2000 Êý¾ÝµÄÈ«ÎÄÖ§³ÖÉæ¼°Á½¸ö¹¦ÄÜ£º¶Ô×Ö·ûÊý¾Ý·¢³ö²éѯµÄÄÜÁ¦ºÍ´´½¨¼°Î¬»¤»ù´¡Ë÷ÒýÒÔ¼ò»¯ÕâЩ²éѯµÄÄÜÁ¦¡£
È«ÎÄË÷ÒýÔÚÐí¶àµØ·½ÓëÆÕͨµÄ SQL Ë÷Òý²»Í¬¡£
ÆÕͨ SQL Ë÷ÒýÈ«ÎÄË÷Òý
´æ´¢Ê±ÊÜ ......
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 ÔËËã·û¡£ÀýÈç£ ......
Ò»¡¢¼Ó¿ìsqlµÄÖ´ÐÐËÙ¶È
¡¡¡¡1.select Óï¾äÖÐʹÓÃsort,»òjoin
¡¡¡¡Èç¹ûÄãÓÐÅÅÐòºÍÁ¬½Ó²Ù×÷£¬Äã¿ÉÒÔÏÈselectÊý¾Ýµ½Ò»¸öÁÙʱ±íÖУ¬È»ºóÔÙ¶ÔÁÙʱ±í½øÐд¦Àí¡£ÒòΪÁÙʱ±íÊǽ¨Á¢ÔÚÄÚ´æÖУ¬ËùÒԱȽ¨Á¢ÔÚ´ÅÅÌÉϱí²Ù×÷Òª¿ìµÄ¶à¡£
¡¡¡¡È磺
SELECT time_records.*, case_name¡¡
from time_records, OUTER cases¡¡
WHERE time_re ......
µ÷Óà MS SQL ±êÁ¿Öµº¯Êý£¬Ó¦¸ÃÔÚº¯ÊýÇ°Ãæ¼ÓÉÏ "dbo."£¬·ñÔò»á±¨ “²»ÊÇ¿ÉÒÔʶ±ðµÄ ÄÚÖú¯ÊýÃû³Æ”´íÎó¡£ÀýÈç
DECLARE @WhichDB TINYINT;
SELECT @WhichDB = user_GetWhichDB(1);--¿´¿´ÊÇÄĸöÊý¾Ý¿âµÄ
=================================================
-- ......