sqlserverのいくつかのよくあるページ

CSDNから回転http://topic.csdn.net/u/20100726/10/ac55bccb-7905-4b50-9056-d19e00c79918.html

   ：
CREATE TABLE [TestTable] (
 [ID] [int] IDENTITY (1, 1) NOT NULL ,
 [FirstName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
 [LastName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
 [Country] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
 [Note] [nvarchar] (2000) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO
 
    ：(2  ，             )
SET IDENTITY_INSERT TestTable ON
declare @i int
set @i=1
while @i<=20000
begin
    insert into TestTable([id], FirstName, LastName, Country,Note) values(@i, 'FirstName_XXX','LastName_XXX','Country_XXX','Note_XXX')
    set @i=@i+1
end
SET IDENTITY_INSERT TestTable OFF
 
-------------------------------------
     ：(  Not In SELECT TOP  )
    ：
SELECT TOP 10 *
FROM TestTable
WHERE (ID NOT IN
          (SELECT TOP 20 id
         FROM TestTable
         ORDER BY id))
ORDER BY ID

SELECT TOP     *
FROM TestTable
WHERE (ID NOT IN
          (SELECT TOP    *   id
         FROM  
         ORDER BY id))
ORDER BY ID
-------------------------------------
     ：(  ID     SELECT TOP  ）
    ：
SELECT TOP 10 *
FROM TestTable
WHERE (ID >
          (SELECT MAX(id)
         FROM (SELECT TOP 20 id
                 FROM TestTable
                 ORDER BY id) AS T))
ORDER BY ID

SELECT TOP     *
FROM TestTable
WHERE (ID >
          (SELECT MAX(id)
         FROM (SELECT TOP    *   id
                 FROM  
                 ORDER BY id) AS T))
ORDER BY ID

-------------------------------------
     ：(  SQL         )
create  procedure XiaoZhengGe
@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
 
     ：      ，      ，        ，      。
       ，       ，       。
 
  SQL      ，    ：     :
     ：(  ID     SELECT TOP  ）    ，    SQL  
     ：(  Not In SELECT TOP  )       ，    SQL  
     ：(  SQL         )        ，      
      ，     。





SELECT TOP 10 * 
FROM 
        (
        SELECT ROW_NUMBER() OVER (ORDER BY tid) AS RowNumber,* FROM tenderInfo
        ) A
WHERE RowNumber > 10*(2-1) 




SELECT TOP 20 *
FROM tenderInfo
WHERE (tid >
          (SELECT isnull(MAX(tid),0) --  max null    0，        
         FROM (SELECT TOP (20*1) tid --   *   id
                 FROM tenderInfo
                 ORDER BY tid) AS T))
ORDER BY tid