ÄãÖªµÀ¶àÉÙ¹ØÓÚsql select top N?
±³¾°:
sql select top N Óï¾äÊÇÒ»¸ö·Ç³£ÖØÒªµÄÓï¾ä, ÔÚʵÏÖ·ÖÒ³²éѯÖÐÊDz»¿É»òȱµÄ. ÓÉÓÚ·ÖÒ³²éѯͨ³£Éæ¼°º¬ÓдóÁ¿¼Ç¼µÄ±í£¬ ²¢ÇÒÊÇΪ´óÁ¿Óû§·ÖÏíµÄÈÎÎñ£¬Òò´Ë£¬¶ÔÆä½øÐÐÓÅ»¯ÊǺÜÓÐÒâÒåµÄ¡£
ʵÏÖsql top N µÄ¹¦ÄÜÓм¸ÖÖ±äÖÖ£º
1. set rowcount @n; select ... order by somefields
2. select top (@n) .... order by somefields
3. select top (xx) .... order by somefields
-- ÆäÖÐ xxÊÇÒ»¸ö³£Êý£¬ ±ÈÈç10
ÔÚÉÏÊöµÄ²éѯÖÐÒýÓõÄsomefields, Èç¹ûÉæ¼°µÄ±íÔÚÆäÉÏÓÐË÷ÒýÊÇÒ»ÖÖÇé¿ö£¬ ûÓÐË÷ÒýÓÖÊÇÒ»ÖÖÇé¿ö¡£
ÓÐË÷ÒýµÄ»°£¬¼´Ê¹±íº¬Óкܶà¼Ç¼£¬Ò²²»»á¶ÔÐÔÄÜÔì³ÉÌ«´óÎÊÌâ¡£
ûÓÐË÷ÒýµÄÇé¿öÒ²ÊÇ»áÓÐʵ¼ÊÐèÇóµÄ£¬±ÈÈçʵʱµÄÕÒ³öÏúÊÛ×îºÃµÄǰ100¸ö²úÆ·¡£ÔÚûÓÐË÷ÒýʱµÄ²éÕÒTop N£¬ Èç¹û²»½øÐÐϸÖµÄÓÅ»¯£¬»á¶ÔÐÔÄÜÔì³ÉºÜ´óµÄÓ°Ï죬ÉõÖÁ»áʹµÃÕû¸öϵͳ̱»¾¡£
Èç¹ûÒª¶Ôtop n½øÐÐÓÅ»¯£¬ÄÇôÁ˽âsql server ÊÇÈçºÎ´¦ÀíÉÏÊöµÄtop n µÄ¼¸ÖÖ±äÐξÍÊǺÜÓбØÒªµÄ. ÏÂÃæµÄÎÄÕÂÊÇÎÒÔÚMSµÄÂÛ̳ÉÏ·¢µÄ£¬ ÎÒ×Ô¼ºÀÁµÃ·Òë³ÉÖÐÎÄÁË£¬ºÍ´ó¼Ò¹²Ïíһϰɡ£
ÔÎÄ(ÊÇÎÒÔÚhttp://social.msdn.microsoft.com/Forums/en/transactsql/thread/944ad896-b34c-4dea-af55-cfbae79251f6ÉϵÄÒ»¸ö»ØÌù)
Question:
--fast
1. select top 100 * from test where c1 < 30000 order by c2
--slow
2. select top 101 * from test where c1 < 30000 order by c2
1. is more than two times faster than 2.
Why?
What a coinccident! I am on the same issue just at the time.
I was considering implementing an algorithm like this:
First populate the N rows to a table variable (with index on the sort column), then iterate through all left rows, adding one row to the table variable if bigger than min of the table, else discard it. This could be either done in sql or clr aggregate function.
Then I thought maybe MS had already done it in the Top N stuff, so started to run a test against it.
CREATE TABLE [dbo].[NUM]
([n] int NOT NULL, s varchar(128) NULL, PRIMARY KEY CLUSTERED([n] ASC))
go
-- populate data
set nocount on
declare @n int, @i int
set @n=1000000
set @i = 0
while @n>0 begin
if @i = 0 begin tran
insert
Ïà¹ØÎĵµ£º
ϵͳ»·¾³£ºWindows 7
Èí¼þ»·¾³£ºVisual C++ 2008 SP1 +SQL Server 2005
±¾´ÎÄ¿µÄ£º±àдһ¸öº½¿Õ¹ÜÀíϵͳ
ÕâÊÇÊý¾Ý¿â¿Î³ÌÉè¼ÆµÄ³É¹û£¬ËäÈ»³É¼¨²»¼Ñ£¬µ«ÊÇ×÷ΪÎÒÓÃVC++ ÒÔÀ´±àдµÄ×î´ó³ÌÐò»¹ÊÇ´«µ½ÍøÉÏ£¬ÒÔ¹©²Î¿¼¡£ÓÃVC++ ×öÊý¾Ý¿âÉè¼Æ²¢²»ÈÝÒ×£¬µ«Ò²²»ÊDz»¿ÉÄÜ¡£ÒÔÏÂÊÇÎҵijÌÐò½çÃæ£¬ºóÃæ ......
SQLÓï·¨ÊÖ²á
Select
ÓÃ;£º
´ÓÖ¸¶¨±íÖÐÈ¡³öÖ¸¶¨µÄÁеÄÊý¾Ý
Óï·¨£º
SELECT column_name(s) from table_name
½âÊÍ£º
´ÓÊý¾Ý¿âÖÐѡȡ×ÊÁÏÁУ¬²¢ÔÊÐí´ÓÒ»»ò¶à¸ö×ÊÁϱíÖУ¬Ñ¡È¡Ò»»ò¶à¸ö×ÊÁÏÁлò×ÊÁÏÐС£SELECT ³ÂÊöʽµÄÍêÕûÓï·¨Ï൱¸´ÔÓ£¬µ«Ö÷Òª×Ó¾ä¿ÉժҪΪ£º
SELECT select_ ......
Ó¦Óû·¾³¾ÍÊdzÌÐòÔÚÁ½¸öµØµãÔËÐеģ¬Á½±ß¶¼Òª²Ù×÷ÐÞ¸ÄÊý¾Ý£¬Á½µØÍ¨¹ý²¦ºÅÁ¬½Ó£¬ÊÇÓÃsql2000·½±ã£¬»¹ÊÇ2005·½±ã£¬ÓÃ2̨»úÆ÷¾Í¿ÉÒÔ»¹ÊÇ3̨»úÆ÷£¬Óô¥·¢Æ÷»¹ÊDZðµÄ·½·¨ÊµÏÖ£¿SQL codeÒÔÏÂʵÏÖ¸´ÖƲ½Ö裨ÒÔ¿ìÕÕ¸´ÖÆÎªÀý£©
ÔËÐÐÆ½Ì¨SQL SERVER 2005
Ò»¡¢×¼±¸¹¤×÷£º
1£®½¨Á¢Ò»¸ö WINDOWS Óû§£¬ÉèÖÃΪ¹ÜÀíԱȨÏÞ£¬²¢ÉèÖÃÃÜ ......
sqlÓïÑÔÖÐÓÐûÓÐÀàËÆCÓïÑÔÖеÄswitch caseµÄÓï¾ä£¿£¿
ûÓÐ,ÓÃcase when À´´úÌæ¾ÍÐÐÁË.
ÀýÈç,ÏÂÃæµÄÓï¾äÏÔʾÖÐÎÄÄêÔÂ
select getdate() as ÈÕÆÚ,case month(get ......
֮ǰʹÓÃPLSQL£¬¶¼ÊÇÒÔдÈëÄÚÈÝΪÖ÷£¬Ð´ÈëÖÐÎÄÄÚÈÝżÓÐÂÒÂ룬µ«ÊÇÖ»ÔÚÏÔʾÉϲ¢Î´Ó°Ïì´æÈ¡£¬ËùÒÔûÓÐÔÚÒâ¡£
½ñÌìÔÚÐÞ¸ÄÒ»¸öÊÓͼʱ£¬ÐèÒª½«ÐÞ¸ÄÄÚÈݼǼÏÂÀ´£¬so,Ïëд¸öÐ޸ļǼ£¬¾Í°ÑÊÓͼÄÚÈÝ¿½±´Á˳öÏÖ£¬·¢ÏÖ¿½±´³öÏÖµÄÖÐÎÄÄÚÈݳöÏÖÂÒÂë¡£
²¢Î´ÍêÈ«ÔÚÒ⣬ÒÔΪֻҪÔÑù·Å»Øplsql¾Í»áºÃµÄ£¬µ«ÊDZ£´æÔÙÈ¡³ö·Åµ½plsqlÖз¢ÏÖÈÔ ......