SQL SERVER 2005¹ý³Ì·ÖÒ³
AspNetPagerÊÇÒ»¸ö±È½ÏºÃµÄ·ÖÒ³¿Ø¼þ,¸Ã¿Ø¼þʹÓùý³Ì·Öҳʮ·Ö·½±ã,¹ý³ÌÈçÏÂ:
Create PROCEDURE [dbo].[pt_AspNetPager]
(
@tableName varchar(255), -- ±íÃû
@fieldName varchar(400)= '*', -- ×Ö¶ÎÃû
@strWhere varchar(255) = Null,
@fieldOrder varchar(100), --²»ÄÜΪ¿Õ
@startIndex int,
@endIndex int
)
AS
declare @strSQL varchar(4000)
begin
set @strSQL='WITH temp AS (SELECT ROW_NUMBER() OVER (ORDER BY '+@fieldOrder+') as row,'+@fieldName+' from '+@tableName
if @strWhere<>''
SELECT @strSQL=@strSQL+' where '+@strWhere
SELECT @strSQL=@strSQL+')'
SELECT @strSQL=@strSQL+' SELECT * from temp where row between '+cast(@startIndex as varchar(10))+' and '+cast(@endIndex as varchar(10))
print @strSQL
exec (@strSQL)
END
Ïà¹ØÎĵµ£º
1 ÓÃUNIONÌæ»»OR (ÊÊÓÃÓÚË÷ÒýÁÐ)
ͨ³£Çé¿öÏÂ, ÓÃUNIONÌæ»»WHERE×Ó¾äÖеÄOR½«»áÆðµ½½ÏºÃµÄЧ¹û. ¶ÔË÷ÒýÁÐʹÓÃOR½«Ôì³ÉÈ«±íɨÃè. ×¢Òâ, ÒÔÉϹæÔòÖ»Õë¶Ô¶à¸öË÷ÒýÁÐÓÐЧ.
Èç¹ûÓÐcolumnûÓб»Ë÷Òý, ²éѯЧÂÊ¿ÉÄÜ»áÒòΪÄãûÓÐÑ¡ÔñOR¶ø½µµÍ. ÔÚÏÂÃæµÄÀý×ÓÖÐ, LOC_ID ºÍREGIONÉ϶¼½¨ÓÐË÷Òý.
¸ßЧ: SELECT LOC_ID , LOC_DESC , ......
MS SQL 2005 32/64λÏÂÔØ
thunder://QUFmdHA6Ly9TaGFyZToxNjQzQHd3dy5zb293ZWIubmV0L1NoYXJlL1NlcnZlci9NaWNyb3NvZnQlMjBTUUwlMjBTZXJ2ZXIlMjAyMDA1L1NRTC4yMDA1LmFsbC5jaHMuaXNvWlo=
Microsoft SQL Server 2005 CHN 32λ CD1
ed2k://|file|%5B%E5%BE%AE%E8%BD%AFSQL%E6%9C%8D%E5%8A%A1%E5%99%A82005%E7%AE%80%E4%BD%93% ......
SQLÄ£ºý²éѯÓï¾äºÍEscapeתÒå×Ö·û(2008-09-22 14:26:01)
±êÇ©£ºfrom תÒå×Ö·û sql µ¥ÒýºÅ ͨÅä·û escape it
ͨÅä·ûÃèÊöʾÀý%°üº¬Áã¸ö»ò¸ü¶à×Ö·ûµÄÈÎÒâ×Ö·û´®¡£WHERE title LIKE '%computer%' ½«²éÕÒ´¦ÓÚÊéÃûÈÎÒâλÖõİüº¬µ¥´Ê computer µ ......
±íÖÐÖ÷¼ü±ØÐëΪ±êʶÁУ¬[ID] int IDENTITY (1,1)
1.·ÖÒ³·½°¸Ò»£º(ÀûÓÃNot InºÍSELECT TOP·ÖÒ³)
Óï¾äÐÎʽ£º
SELECT TOP Ò³¼Ç¼ÊýÁ¿ *
from ±íÃû
WHERE (ID NOT IN
(SELECT TOP (ÿҳÐÐÊý*(Ò³Êý-1)) ID
from ±íÃû
ORDER BY ID))
ORDER BY ID
//×Ô¼º»¹¿ÉÒÔ¼ÓÉÏһЩ²éѯ ......
ÔÚsql Server ´Ó¿ª·¢»·¾³ÍùÉú²ú»·¾³²¿Êðʱ£¬ÒòΪ¿Í»§ÄÇÀïÓõÄÊÇÓ¢Îİ汾£¬ËùÒÔ´´½¨Êý¾Ý¿âÒÔºó£¬ÒªÉèÖÃÒ»ÏÂÊý¾Ý¿âÅÅÐò¹æÔò¡£
µ«ÊÇÔÚ´æ´¢¹ý³ÌÖУ¬ÓеijÌÐòԱʹÓÃÁËÁÙʱ±í£¬ÒòΪÁÙʱ±íÊÇ´æ´¢ÔÚϵͳÊý¾Ý¿âtempdbÖУ¬Èç¹ûϵͳÁÙʱÊý¾Ý¿âûÓнøÐÐÅÅÐò¹æÔòµÄÉèÖ㬳ÌÐò»¹Êdzö´í£¬½â¾ö°ì·¨¾ÍÊÇÉèÖÃÁÙʱÊý¾Ý¿âµÄÅÅÐò¹æÔò»òÕß´´½¨ÁÙÊ ......