ÈýÖÐSQL ·ÖÒ³·½·¨Ð§ÂÊ·ÖÎö

ÈýÖÖSQL·ÖÒ³·¨Ð§ÂÊ·ÖÎö
±íÖÐÖ÷¼ü±ØÐëΪ±êʶÁУ¬[ID] int IDENTITY (1,1)
1.·ÖÒ³·½°¸Ò»£º(ÀûÓÃNot InºÍSELECT TOP·ÖÒ³)
¡¡Óï¾äÐÎʽ£ºÀûÓÃNot InºÍSELECT TOP·ÖÒ³) ЧÂÊÖУ¬ÐèҪƴ½ÓSQLÓï¾ä
  SELECT TOP 10 * from  TestTable WHERE (Id  NOT  IN (SELECT  TOP  20    id  from        TestTable ORDER BY id )) ORDER BY ID
2.·ÖÒ³·½°¸¶þ£º(ÀûÓÃID´óÓÚ¶àÉÙºÍSELECT TOP·ÖÒ³)
  Óï¾äÐÎʽ£ºÀûÓÃID´óÓÚ¶àÉÙºÍSELECT TOP·ÖÒ³)ЧÂÊ×î¸ß£¬ÐèҪƴ½ÓSQLÓï¾ä
  SELECT TOP 10 * from  TestTable WHERE (ID  > (SELECT    MAX(id)    from (SELECT  TOP    20  id from    TestTable ORDER BY id) AST))
3.·ÖÒ³·½°¸Èý£º(ÀûÓÃSQLµÄÓαê´æ´¢¹ý³Ì·ÖÒ³)
Óï¾äÐÎʽ£ºÀûÓÃSQLµÄÓαê´æ´¢¹ý³Ì·ÖÒ³) ЧÂÊ×î²î£¬µ«ÊÇ×îΪͨÓÃ
  create procedure SqlPager
@sqlstr nvarchar(4000),                --²éѯ×Ö·û´®
@currentpage int,                      --µÚNÒ³
@pagesize int                        --ÿҳÐÐÊý
as
set nocount on
declare @P1 int,                      --P1ÊÇÓαêµÄid
  @rowcount int
exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output
select ceiling(1.0*@rowcount/@pagesize) as ×ÜÒ³Êý--,@rowcount as ×ÜÐÐÊý,@currentpage as µ±Ç°Ò³
set @currentpage=(@currentpage-1)*@pagesize+1
exec sp_cursorfetch @P1,16,@currentpage,@pagesize
exec sp_cursorclose @P1
set nocount off