???????????web ????????????????????????????????????????????????????????????????????:ADO ???????????????????ADO?????????????????α???????????????????????????????С???????????Σ?????α?????????α?????????? ?У??????檔?α????????????????????????????α??α?????????????????????????Σ????????α??????б????????????????????????? ??????в?????????????????????ж?????α??????????????????????????????????????????????????
?????????????????????????????????????????????????????????ζ???????????????????????????????????????е???????????????????С??????????????????????е?????????????е???С?
???????????????????????С???????????????????????“??????洢????”??????洢?????????α??????α???????????????????????е???????????ɡ?
????????????????????????洢??????????洢??????????????????????д?????洢?????
1 CREATE procedure pagination1
2
3 (@pagesize int?? --????С???????洢20?????
4
5 @pageindex int --??????
6
7 )
8
9 as
10
11 set nocount on
12
13 begin
14
15 declare @indextable table(id int identity(1??1)??nid int) --????????
16
17 declare @PageLowerBound int --???????????
18
19 declare @PageUpperBound int --???????????
20
21 set @PageLowerBound=(@pageindex-1)*@pagesize
22
23 set @PageUpperBound=@PageLowerBound+@pagesize
24
25 set rowcount @PageUpperBound
26
27 insert into @indextable(nid) select gid from TGongwen where fariqi >dateadd(day??-365??getdate()) order by fariqi desc
28
29 select O.gid??O.mid??O.title??O.fadanwei??O.fariqi from TGongwen O??@indextable t where O.gid=t.nid
30
31 and t.id>@PageLowerBound and t.id<=@PageUpperBound order by t.id
32
33 end
34
35 set nocount off
????????洢??????????SQL SERVER????????????????洢??????????????????洢????????????????????У????????????е?????д????? ???CREATE TABLE #Temp?????????????SQL SERVER?У????????????????????????????????????????洢????????о?????????????????????ADO??á????????????????????? ????????????
???????????????????????С???????????????????n??????m?????????????????????￡?
??????publish ????????? n ?????? m ????????
????1 SELECT TOP m-n+1 *
????2 FROM publish
????3 WHERE (id NOT IN
????4 ????????(SELECT TOP n-1 id
????5 ???????? FROM publish))
????id ?publish ???????
??????????????????μ??????????????????????·????úá???????????????????????????ASP.NET+ C#??SQL SERVER??????????????????????￡?????????????????????￡????????????????????洢?????????????????????????￡?????????? ????????????????????????д?????????洢?????????洢???????????????е???????洢?????????????????????????????洢?????
1 CREATE PROCEDURE pagination2
2 (
3 @SQL nVARCHAR(4000)?? --????????????SQL???
4 @Page int?? --???
5 @RecsPerPage int?? --???????????
6 @ID VARCHAR(255)?? --??????????????ID??
7 @Sort VARCHAR(255) --??????μ?????
8 )
9 AS
10
11 DECLARE @Str nVARCHAR(4000)
12
13 SET @Str='SELECT TOP '+CAST(@RecsPerPage AS VARCHAR(20))+' * FROM ('+@SQL+') T WHERE T.'+@ID+'NOT IN
14 (SELECT TOP '+CAST((@RecsPerPage*(@Page-1)) AS VARCHAR(20))+' '+@ID+' FROM ('+@SQL+') T9 ORDER BY '+@Sort+') ORDER BY '+@Sort
15
16 PRINT @Str
17
18 EXEC sp_ExecuteSql @Str
19 GO
???????????????????????
1 SELECT TOP ???С *
2
3 FROM Table1
4
5 WHERE (ID NOT IN
6
7 (SELECT TOP ???С*??? id
8
9 FROM ??
10
11 ORDER BY id))
12
13 ORDER BY ID
??????????洢??????????????????????????NOT IN????????????????????????
????1 SELECT TOP ???С *
????2
????3 FROM Table1
????4
????5 WHERE not exists
????6
????7 (select * from (select top (???С*???) * from table1 order by id) b where b.id=a.id )
????8
????9 order by id
??????????not exists??????not in????????????????????????????Ч??????????????????
?????????????TOP ???NOT IN?????????????????α????????Щ??
?????????not exists?????????????洢?????Ч????????SQL SERVER?е?TOP????????????????????????????????????????????????????????????????????????????TOP??????????TOP ????????????????????
???????????У??????????????????????????TOP??NOT IN??TOP????????????????????NOT IN???????????????????????????????????????????????????NOT IN????????????????????
??????????????????κ???Σ?????????????max(???)??min(???)????????????е???С???????????????β????????????? ??????Щ?????????ε?max??min???????????????????з?????????????????????????ò?????“>”??“<”????????? ?????????????????SARG??????磺
????Select top 10 * from table1 where id>200
???????????????·????????
1 select top ???С *
2
3 from table1
4
5 where id>
6
7 (select max (id) from
8
9 (select top ((???-1)*???С) id from table1 order by id) as T
10
11 )
12
13 order by id