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

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 'ºÄʱ(ºÁÃë)'
----------------------------------------------------------------------------------------------------------
²âÊÔ·½·¨¼°½á¹û(È¡


Ïà¹ØÎĵµ£º

SQL Server Ë÷Òý½á¹¹¼°ÆäʹÓ㨶þ£©

¸ÄÉÆSQLÓï¾ä
¡¡¡¡ºÜ¶àÈ˲»ÖªµÀSQLÓï¾äÔÚSQL SERVERÖÐÊÇÈçºÎÖ´Ðеģ¬ËûÃǵ£ÐÄ×Ô¼ºËùдµÄSQLÓï¾ä»á±»SQL SERVERÎó½â¡£±ÈÈ磺
select * from table1 where name=''zhangsan'' and tID > 10000
ºÍÖ´ÐÐ:
select * from table1 where tID > 10000 and name=''zhangsan''
¡¡¡¡Ò»Ð©È˲»ÖªµÀÒÔÉÏÁ½ÌõÓï¾äµÄÖ´ÐÐЧÂÊÊÇ·ñÒ» ......

SQLÖÐDATEADDºÍDATEDIFFµÄÓ÷¨(ÈÕÆÚ)

SQLÖÐDATEADDºÍDATEDIFFµÄÓ÷¨
2009-12-10 22:16
¹Ì¶¨ÈÕÆÚ¼õÒ»¸öÔ£¨ÈÕ¡¢Ä꣩
dateadd(±í´ïʽ1£¬±í´ïʽ2£¬±í´ïʽ3£©
±í´ïʽ1ΪMM»òYY»òDD
±í´ïʽ2Ϊ1
±í´ïʽ3ΪҪ¼õµÄÈÕÆÚ
Ò»¸öÔµĵÚÒ»Ìì
µÚÒ»¸öÀý×Ó£¬ÎÒ½«¸æËßÄãÈçºÎ´Óµ±Ç°ÈÕÆÚÈ¥Õâ¸öÔµÄ×îºóÒ»Ìì¡£Çë×¢Ò⣺Õâ¸öÀý×ÓÒÔ¼°ÕâÆªÎÄÕÂÖÐµÄÆäËûÀý×Ó¶¼½«Ö»Ê¹ÓÃDATEDIFF ......

ÔÚJAVA Ô´³ÌÐòÖбàдSQLÓï¾äʱʹÓÃORACLE °ó¶¨±äÁ¿

ÔÚJAVAÖеÄSQL Óï¾äµÄ±àд·½Ã棬ûÓÐʹÓÃORACLE °ó¶¨±äÁ¿£¬ºÜ´ó³Ì¶ÈÉϽµµÍÁËÊý¾Ý¿âµÄÐÔÄÜ£¬±íÏÖÔÚÁ½¸ö·½Ã棺
 
1¡¢SQLÓï¾äÓ²·ÖÎö(Hard Parse)Ì«¶à£¬ÑÏÖØÏûºÄCPU×ÊÔ´£¬ÑÓ³¤ÁËSQLÓï¾ä×ܵÄÖ´ÐÐʱ¼ä
SQLÓï¾äµÄÖ´Ðйý³Ì·Ö¼¸¸ö²½Ö裺Óï·¨¼ì²é¡¢·ÖÎö¡¢Ö´ÐС¢·µ»Ø½á¹û¡£ÆäÖзÖÎöÓÖ·ÖΪӲ·ÖÎö(Hard Parse)ºÍÈí·ÖÎö(Soft Pars ......

SQLË÷Òý

ʹÓÃË÷Òý¿É¿ìËÙ·ÃÎÊÊý¾Ý¿â±íÖеÄÌØ¶¨ÐÅÏ¢¡£Ë÷ÒýÊǶÔÊý¾Ý¿â±íÖÐÒ»Áлò¶àÁеÄÖµ½øÐÐÅÅÐòµÄÒ»Öֽṹ£¬ÀýÈç employee ±íµÄÐÕ£¨lname£©ÁС£Èç¹ûÒª°´ÐÕ²éÕÒÌØ¶¨Ö°Ô±£¬Óë±ØÐëËÑË÷±íÖеÄËùÓÐÐÐÏà±È£¬Ë÷Òý»á°ïÖúÄú¸ü¿ìµØ»ñµÃ¸ÃÐÅÏ¢¡£
¡¡¡¡Ë÷ÒýÊÇÒ»¸öµ¥¶ÀµÄ¡¢ÎïÀíµÄÊý¾Ý¿â½á¹¹£¬ËüÊÇij¸ö±íÖÐÒ»ÁлòÈô¸ÉÁÐÖµµÄ¼¯ºÏºÍÏàÓ¦µÄÖ¸Ïò±íÖÐÎïÀ ......

sqlÓëhqlµÄÇø±ð

sqlÊǹØÏµÊý¾Ý¿â²éѯÓïÑÔ,Ãæ¶ÔµÄÊý¾Ý¿â;
¶øhqlÊÇHibernateÕâÑùµÄÊý¾Ý¿â³Ö¾Ã»¯¿ò¼ÜÌṩµÄÄÚÖòéѯÓïÑÔ,ËäÈ»ËûÃǵÄÄ¿µÄ¶¼ÊÇΪÁË´ÓÊý¾Ý¿â²éѯÐèÒªµÄÊý¾Ý,µ«sql²Ù×÷µÄÊÇÊý¾Ý¿â±íºÍ×Ö¶Î,
¶ø×÷ÎªÃæÏò¶ÔÏóµÄhql²Ù×÷µÄÔòÊdz־û¯À༰ÆäÊôÐÔ¡£
¾ÙÒ»¸ö¼òµ¥µÄÀý×Ó
sqlÓï¾ä
select * from tb_goods where id in (select goodsid f ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ