ÄãÖªµÀ¶àÉÙ¹ØÓÚ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
Ïà¹ØÎĵµ£º
SQLÓï·¨ÊÖ²á
Select
ÓÃ;£º
´ÓÖ¸¶¨±íÖÐÈ¡³öÖ¸¶¨µÄÁеÄÊý¾Ý
Óï·¨£º
SELECT column_name(s) from table_name
½âÊÍ£º
´ÓÊý¾Ý¿âÖÐѡȡ×ÊÁÏÁУ¬²¢ÔÊÐí´ÓÒ»»ò¶à¸ö×ÊÁϱíÖУ¬Ñ¡È¡Ò»»ò¶à¸ö×ÊÁÏÁлò×ÊÁÏÐС£SELECT ³ÂÊöʽµÄÍêÕûÓï·¨Ï൱¸´ÔÓ£¬µ«Ö÷Òª×Ó¾ä¿ÉժҪΪ£º
SELECT select_ ......
CREATE FUNCTION [dbo].[f_num_chn] (@num numeric(14,2))
RETURNS varchar(100) WITH ENCRYPTION --¼ÓÃÜ
AS
BEGIN
--°æÈ¨ËùÓУºpbsql
DECLARE @n_data VARCHAR(20),@c_data VARCHAR(100),@n_str VARCHAR(10),@i int
SET @n_data=RIGHT(SPACE(14)+CAST(CAST(ABS(@num*100) AS bigint) AS varchar(20)), ......
Ó¦Óû·¾³¾ÍÊdzÌÐòÔÚÁ½¸öµØµãÔËÐеģ¬Á½±ß¶¼Òª²Ù×÷ÐÞ¸ÄÊý¾Ý£¬Á½µØÍ¨¹ý²¦ºÅÁ¬½Ó£¬ÊÇÓÃsql2000·½±ã£¬»¹ÊÇ2005·½±ã£¬ÓÃ2̨»úÆ÷¾Í¿ÉÒÔ»¹ÊÇ3̨»úÆ÷£¬Óô¥·¢Æ÷»¹ÊDZðµÄ·½·¨ÊµÏÖ£¿SQL codeÒÔÏÂʵÏÖ¸´ÖƲ½Ö裨ÒÔ¿ìÕÕ¸´ÖÆÎªÀý£©
ÔËÐÐÆ½Ì¨SQL SERVER 2005
Ò»¡¢×¼±¸¹¤×÷£º
1£®½¨Á¢Ò»¸ö WINDOWS Óû§£¬ÉèÖÃΪ¹ÜÀíԱȨÏÞ£¬²¢ÉèÖÃÃÜ ......
Õâ¾ÍÒª´ÓË«ÒýºÅºÍµ¥ÒýºÅµÄ×÷Óý²Æð£º
Ë«ÒýºÅÀïÃæµÄ×ֶλᾹý±àÒëÆ÷½âÊÍÈ»ºóÔÙµ±×÷HTML´úÂëÊä³ö£¬µ«Êǵ¥ÒýºÅÀïÃæµÄ²»ÐèÒª½âÊÍ£¬Ö±½ÓÊä³ö¡£ÀýÈ磺
$abc='I love u';
echo $abc //½á¹ûÊÇ:I love u
echo '$abc' //½á¹ûÊ ......