|
( P8 _# x! Y1 S$ F2 i( r/ H: l- --创建存储过程
- D" S. v# s7 X; x4 m - if exists(select * from sysobjects where name='P_GetStudentInfoPage')
- U! J, ?& C, a5 f' Y& o. b! R - drop proc P_GetStudentInfoPage6 Q( E p* Z7 \1 E# y* x% |
- go9 t1 H a1 T e/ R
- create proc [dbo].P_GetStudentInfoPage
% m$ y- J$ d% J- S2 @: W! C1 Q! b - (
) g7 H( N }, K9 T: \ - @pageIndex int=1,--页号
% z2 O. o, j) v0 ~+ v0 U ~9 _ - @pageSize int=10--每页数据条数/ d G2 W: F% Y$ U9 a. W5 n/ W
- )
' G( K2 v( ?% v& o - as
) V/ M5 J8 r- }7 ]' k; Z0 } - --select count(*) from StudentInfo --查询数据总数
) ]9 C7 c2 q& }1 ^" s9 e: M0 D - ; y; @- |) h. q+ a$ @1 J: n8 C
- select * from
4 R% C) o U* n, C I5 i - (select ROW_NUMBER() over(order by StudentNo) as rowNum,* from StudentInfo) as tbl
2 j6 u1 K9 R" c {+ Y6 h - where tbl.rowNum between ((@pageIndex - 1)* @pageSize + 1) and (@pageIndex*@pageSize)
7 u: r9 t& \$ X5 }9 e
* Y/ X0 V V6 z. S- A- GO* a6 ~3 W; k7 z' O0 {
- , M1 B" Y+ e9 H5 d3 c; j; e+ F
- --调用
]7 D1 d1 L' F% \0 j% m - 7 p+ d) u' Q R( o6 c6 M
- EXEC P_GetStudentInfoPage 1,10, C+ I4 J" c u A
+ R9 ]" n$ S ?( y7 b1 D- EXEC P_GetStudentInfoPage 2,105 Q! Q# ~1 R* ?( ]
- $ Q% h2 R% i( Z
- --------------------------------------直接查询代码
4 s& s: h8 m" H F) t - select * from StudentInfo where id>=1 and id<=10
) [. I2 Y V; O3 ^ - select * from StudentInfo where id>=10 and id<=20
4 k V: Y1 p# v/ J - % \! D: i1 H2 N( N9 i4 u) ~, g
- select * from StudentInfo where id between (1-1)*10+1 and 10 *11 T* _0 U" b9 S5 m; r
- select * from StudentInfo where id between (2-1)*10+1 and 10 *25 d- a0 R2 J( U |& e
- 6 W) l. I* r5 @
- select (1-1)*10+1 --13 ]4 u. n5 b/ t$ W3 Z
- select (2-1)*10+1 --11! K* B! O0 J. _1 v4 s
- select (3-1)*10+1 --21; C ?' j# B8 R! l, b1 ^
4 T6 L& E% F5 L" ]; b2 c0 y* p- select * from StudentInfo where id between 11 and 20; S* K; [$ g6 I! V, p4 @3 {6 m
# e/ }) H! k; C" K! u9 Y0 l- select * from8 T5 F( t i6 y* k
- (select ROW_NUMBER() over(order by StudentNo desc) newRows,* from StudentInfo) t4 Q% j) u+ a' |( }8 J* \" o
- where t.newRows between (1-1)*10+1 and 10 *1
复制代码 |
|