Sql Server 2005 row_number()·ÖÒ³ÐÔÄܲâÊÔ
ÏÖÔÚ·ÖÒ³·½·¨´ó¶à¼¯ÖÐÔÚselect top/not in/Óαê/row_number£¬¶øselect top·ÖÒ³(ÔÚÕâ»ù´¡ÉÏ»¹Óжþ·Ö·¨)·½·¨Ëƺõ¸üÊÜ´ó¼Ò»¶Ó£¬ÕâÆªÎÄÕ²¢²»´òËãÈ¥ÌÖÂÛÊÇ·ñͨÓõÄÎÊÌ⣬±¾×ÅʵÓõÄÔÔò£¬»¨ÁËһЩʱ¼äÈ¥²âÊÔrow_number()·ÖÒ³µÄÐÔÄÜ£¬¸Ð¾õ²¢²»ÏñÒ»²¿·ÖÈËËù˵µÄÄÇô¼¦Àߣ¬ÓÉÓÚ½Ó´¥Èí¼þ¿ª·¢²ÅÊ®¸öÔ£¬·½·½ÃæÃæµÄ¶«Î÷¶¼ÒªÑ§£¬¾ÑéʵÔÚÓÐÏÞ,²»×ãÖ®´¦ÇëÔÁ£¬²âÊÔÈçÏ£º
ƽ̨Óë»·¾³£º
CPU:AMD 1150 2G µ¥ºË
Äڴ棺1G(ϵͳÕý³£Æô¶¯ºóÔ¼Õ¼300M¿Õ¼ä)
Ó²ÅÌ£ºSATA 160G 8M Cache
ϵͳ£ºwindows 2003 ent+Sql Server 2005 sp2
Êý¾Ý£º¹²500ÍòÌõ
-------------------------------------------------------------------
²âÊÔÊý¾Ý£º
create table test_table
(
id int identity(1,1) primary key not null,
cid int not null,
userName varchar(50) null,
userPwd varchar(50) null,
createTime datetime null
)
---------------------------------------------------------------------
²åÈë¼Ç¼(cid·Ö±ð²åÈë1,2,3,4,»úÆ÷ʵÔÚÌ«Âý,×ܹ²Ö»²åÈë500ÍòÌõ)£º
declare @count int
set @count=1
while @count<=1000000
begin
insert into test_table(cid,userName,userPwd,createTime) values(2,'admin','admin888',getdate())
set @count=@count+1
end
-------------------------------------------------------------------------------------------------------
·ÖÒ³²âÊÔ´úÂ룺
ÕâÀï²ÉÓÃrow_numberµÄÁ½ÖÖ·ÖÒ³·½Ê½£º·Ö±ðÓÃtopºÍbetween¹ýÂË
/*row_number() ²éѯ·½·¨Ò»*/
declare @tdiff datetime
set @tdiff=getdate()
select top 20 * from(select row_number() over(order by createtime desc,id asc) as rownumber,* from test_table ) as tb where rownumber>120000
select datediff(ms,@tdiff,getdate()) as 'ºÄʱ(ºÁÃë)'
/*row_number() ²éѯ·½·¨¶þ*/
declare @tdiff datetime
set @tdiff=getdate()
select * from(select row_number() over(order by createtime desc,id asc) as rownumber,* from test_table ) as tb where rownumber between 120000 and 120200
select datediff(ms,@tdiff,getdate()) as 'ºÄʱ(ºÁÃë)'
----------------------------------------------------------------------------------------------------------
²âÊÔ·½·¨¼°½á¹û(È¡
Ïà¹ØÎĵµ£º
ÎÒÏÈÅ×שÒýÓñ:
1. ¿ª·¢ÐÔ: SQLSERVER²»ÄÜ,ORACLEÄÜ×°ÔÚUNIXÉÏ
2. ·ÖÇø±í: SQLSERVER²»Ä ......
ÎÒÃÇÐèÒª¶à´ÎÔËÐÐÒ»¸öÎļþ¼ÐÏÂËùÓеÄsql½Å±¾£¨¿ÉÄÜʱtxt»òÕßsqlµÈÎı¾Îļþ£©À´¸üÐÂSchema£¬Îļþ¶àµÄʱºòÐèÒª¶à´ÎÖ´ÐУ¬ÄÇô¿ÉÒÔʹÓöàÌõÓï¾äÀ´Ö´ÐÐsql½Å±¾¡£
--ÏÂÃæµÄ²éѯÓï¾äÔÚSQL QueryÖÐÔËÐУ¬½«Ö´ÐÐd:\vssÏÂÃæµÄÖ¸¶¨sql½Å±¾£¬ÕâЩ½Å±¾µÄºó׺¶¼ÊÇtxt
exec master..xp_cmdshell 'isql -U sa -P 123 -i d:\vss\MSS ......
ʵÏÖСÊý¾ÝÁ¿ºÍº£Á¿Êý¾ÝµÄͨÓ÷ÖÒ³ÏÔʾ´æ´¢¹ý³Ì
¡¡¡¡½¨Á¢Ò»¸ö Web Ó¦Ó㬷ÖÒ³ä¯ÀÀ¹¦Äܱز»¿ÉÉÙ¡£Õâ¸öÎÊÌâÊÇÊý¾Ý¿â´¦ÀíÖÐÊ®·Ö³£¼ûµÄÎÊÌâ¡£¾µäµÄÊý¾Ý·ÖÒ³·½·¨ÊÇ:ADO ¼Í¼¼¯·ÖÒ³·¨£¬Ò²¾ÍÊÇÀûÓÃADO×Ô´øµÄ·ÖÒ³¹¦ÄÜ£¨ÀûÓÃÓα꣩À´ÊµÏÖ·ÖÒ³¡£µ«ÕâÖÖ·ÖÒ³·½·¨½öÊÊÓÃÓÚ½ÏСÊý¾ÝÁ¿µÄÇéÐΣ¬ÒòΪÓα걾ÉíÓÐȱµã£ºÓαêÊÇ´æ·ÅÔÚÄÚ´æÖУ¬º ......
×÷Õߣºfreedk
Ò»¡¢ÉîÈëdz³öÀí½âË÷Òý½á¹¹
¶þ¡¢¸ÄÉÆSQLÓï¾ä
Èý¡¢ÊµÏÖСÊý¾ÝÁ¿ºÍº£Á¿Êý¾ÝµÄͨÓ÷ÖÒ³ÏÔʾ´æ´¢¹ý³Ì
¾Û¼¯Ë÷ÒýµÄÖØÒªÐÔºÍÈçºÎÑ¡Ôñ¾Û¼¯Ë÷Òý
¡¡¡¡ÔÚÉÏÒ»½ÚµÄ±êÌâÖУ¬±ÊÕßдµÄÊÇ£ºÊµÏÖСÊý¾ÝÁ¿ºÍº£Á¿Êý¾ÝµÄͨÓ÷ÖÒ³ÏÔʾ´æ´¢¹ý³Ì¡£ÕâÊÇÒòΪÔÚ½«±¾´æ´¢¹ý³ÌÓ¦ÓÃÓÚ“°ì¹«×Ô¶¯»¯”ϵͳµÄʵ¼ùÖÐʱ£¬±ÊÕß·¢ÏÖÕ ......
SQL²Ù×÷È«¼¯
ÏÂÁÐÓï¾ä²¿·ÖÊÇMssqlÓï¾ä£¬²»¿ÉÒÔÔÚaccessÖÐʹÓá£
SQL·ÖÀࣺ
DDL—Êý¾Ý¶¨ÒåÓïÑÔ(CREATE£¬ALTER£¬DROP£¬DECLARE)
DML—Êý¾Ý²Ù×ÝÓïÑÔ(SELECT£¬DELETE£¬UPDATE£¬INSERT)
DCL—Êý¾Ý¿ØÖÆÓïÑÔ(GRANT£¬REVOKE£¬COMMIT£¬ROLLBACK)
Ê×ÏÈ,¼òÒª½éÉÜ»ù´¡Óï¾ä£º
1¡¢ËµÃ÷£º´´½¨Êý¾Ý¿â
CREATE ......