上次讲到了用到的是not in

接下来用到 max 或者 min

还有sql server 2005以上的 row_number()系统函数

 

select

 

*

 

from

 Xk_FriendLink

--

取出前面的最大id

select

 

max

(id) 

from

 (

select

 

top

 

2

 id 

from

 Xk_FriendLink 

order

 

by

 id 

asc

) 

as

 t

--

顺序时从小到大

select

 

top

 

2

 

*

 

from

 Xk_FriendLink 

where

 id

>

(

select

 

max

(id) 

from

 (

select

 

top

 

2

 id 

from

 Xk_FriendLink 

order

 

by

 id 

asc

) 

as

 t) 

order

 

by

 id 

asc

--

Row_Number()  自增的,不重复，中间不会有断点的，连续的

select

 

*

,row_number() 

OVER

 (

order

 

by

 id 

desc

) 

as

 RowNumber 

from

 Xk_FriendLink

select

 row_number() 

OVER

 (

order

 

by

 id 

desc

) 

as

 RowNumber 

from

 Xk_FriendLink

--

每页大小 PageSize=3,页码 PageIndex=2,between PageSize*(PageIndex-1)+1 and PageSize*PageIndex

declare

 

@PageSize

 

int

set

 

@PageSize

=

3

declare

 

@PageIndex

 

int

set

 

@PageIndex

=

2

declare

 

@startRow

 

int

set

 

@startRow

=

@PageSize

*

(

@PageIndex

-

1

)

+

1

declare

 

@endRow

 

int

set

 

@endRow

=

@PageSize

*

@PageIndex

print

 

@startRow

print

 

@endRow

select

 

*

 

from

 (

select

 

*

,row_number() 

OVER

 (

order

 

by

 id 

desc

) 

as

 RowNumber 

from

 Xk_FriendLink)

as

 tmp 

where

 RowNumber 

between

 

@startRow

 

and

 

@endRow