sqlserver怎么进行分页,sqlserver2008分页

sqlserver分页

sqlserver分页有四种方法，先给大家介绍一种常用的

查看1到3条数据

假如查询每页大小为3，查询第2页的数据就是

相关问答

Q1: 对sqlserver 语句进行分页

declare @iPage int --所取的页的序数 (第几页)

declare @iPageNum int --所取的数据行数(除最后一页外，每页所取的行数应该都是一样的)

declare @iRecordNumber int --取@iPage页时，结尾记录所在行数

declare @iRecordCount int --总记录行数

--变量初始化

set @iPage=1 --可用参数传递

set @iPageNum=10 --默认每页取10行

set @iRecordNumber=@iPage*@iPageNum

select @iRecordCount=count(cirDeId) from (select cirDeId from CircleDetails group by cirDeId) a

--取最后一页时，重算结尾所在行数(@iRecordNumber)、所取记录数(@iPageNum)

if @iRecordNumber@iRecordCount

begin

set @iRecordNumber=@iRecordCount

if (@iRecordNumber%@iPageNum)0

set @iPageNum=@iRecordNumber%@iPageNum

end

select * from (

select top @iPageNum * from(

select top @iRecordNumber cirDeId,

(select count(1) from CirclePost where CircleDetails.cirDeId = CirclePost.cirdeid_cp) circlepostcount

,(select count(1) from CircleAttention where CircleDetails.cirDeId = CircleAttention.cirdeid_ca) circleattentioncount

from CircleDetails group by cirDeId order by 3 desc,2 desc

) order by 3,2

) order by 3 desc,2 desc

Q2: SQLSERVER如何实现分页查询？

写存储过程 ..

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

ALTER PROCEDURE usp_Province_pagination

@PageSize INT, --每页的显示的行数

@AbsolutePage INT, -- 当前页的页数

@PageCount INT OUTPUT --总页数

AS

DECLARE @BeginRecord INT --记录每此从哪一行开始读取

DECLARE @RecordCount INT --表中数据的总条数

DECLARE @sql NVARCHAR(1000)

SET @RecordCount = (SELECT count(*) FROM Province)

--表中没有数据的情况

IF @RecordCount = 0

BEGIN

SET @PageCount = 0

RETURN(0)

END

-- 表中的总条数大于定义的每页的行数的情况

IF @RecordCount @PageSize

BEGIN

-- 计算总能分多少页

SET @PageCount = (@RecordCount + @PageSize - 1)/@PageSize

--当前应该从哪一行开始读取

SET @BeginRecord = ((@AbsolutePage-1) * @PageSize)

SET @sql = N'SELECT TOP ' + cast(@PageSize AS NVARCHAR(100)) +' ProvinceID, provinceCode, ProvinceName

FROM Province

WHERE ProvinceID NOT IN

(SELECT TOP '+ CAST(@BeginRecord AS NVARCHAR(100)) + ' ProvinceID

FROM Province)'

EXECUTE sp_executesql @sql

END

ELSE -- -- 表中的总条数大于定义的每页的行数情况

BEGIN

SET @PageCount = 1

SET @SQL = 'SELECT ProvinceID, provinceCode, ProvinceName FROM Province '

EXECUTE sp_executesql @sql

END

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

Q3: 数据库sqlserver如何用存储过程做分页

存储过程：create Procedure pname

( @pageIndex int，@pageSize)

as

select * from tableName order by id

offset @pageIndex * pageSize fetch next pageSize rows only

分页：

sqlserver 在2008之前 使用 top 和 not int top 的方式来做分页

2008以后使用 row_number() 函数作为分页关键函数

2012使用 offset 1 fetch next 10 rows only

你问了2个问题，你可以优先把视图，存储过程，触发器等弄明白，分页是查询，在存储过程里可以写复杂的sql文，只是在运行时是预编译和参数化查询防止sql注入

Q4: sqlserver 2005数据库 怎么进行分页

分两步实现

一、分页的存储过程如下

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

CREATE PROCEDURE [dbo].[Pagination]

@tblName varchar(255), -- 表名

@strGetFields varchar(1000) , -- 需要返回的列

@fldName varchar(255), -- 排序的字段名

@PageSize int, -- 页尺寸

@PageIndex int , -- 页码

@doCount bit=0, -- 返回记录总数, 非 0 值则返回

@OrderType bit, -- 设置排序类型, 非 0 值则降序

@strWhere varchar(1500) -- 查询条件 (注意: 不要加 where)

AS

declare @strSQL varchar(5000) -- 主语句

declare @strTmp varchar(110) -- 临时变量

declare @strOrder varchar(400) -- 排序类型

if @doCount != 0

begin

if @strWhere !=''

set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere

else

set @strSQL = 'select count(*) as Total from [' + @tblName + ']'

end

--以上代码的意思是如果@doCount传递过来的不是0，就执行总数统计。以下的所有代码都是@doCount为0的情况

else

begin

if @OrderType != 0

begin

set @strTmp = '(select min'

set @strOrder = ' order by [' + @fldName + '] desc'

end

--如果@OrderType不是0，就执行降序，这句很重要！

else

begin

set @strTmp = '(select max'

set @strOrder = ' order by [' + @fldName + '] asc'

end

if @PageIndex = 1

begin

if @strWhere != ''

set @strSQL = 'select top ' + str(@PageSize) + ' ' + @strGetFields + ' from [' + @tblName + '] where '

+ @strWhere + ' ' + @strOrder

else

set @strSQL = 'select top ' + str(@PageSize) + ' ' + @strGetFields

+ ' from [' + @tblName + '] ' + @strOrder

end

--如果是第一页就执行以上代码，这样会加快执行速度

else

begin

--以下代码赋予了@strSQL以真正执行的SQL代码

set @strSQL = 'select top ' + str(@PageSize) + ' ' + @strGetFields + ' from ['

+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['+ @fldName + ']) from (select top '

+ str( ( @PageIndex - 1 ) * @PageSize ) + ' ['+ @fldName + '] from [' + @tblName + ']'

+ @strOrder + ') as tblTmp)' + @strOrder

if @strWhere != ''

set @strSQL = 'select top ' + str(@PageSize) + ' ' + @strGetFields + ' from ['

+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['

+ @fldName + ']) from (select top ' + str( ( @PageIndex - 1 ) * @PageSize ) + ' ['

+ @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' '

+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder

end

end

exec(@strSQL)

二、页面调用部分代码

Function navindex(ByVal PageIndextemp As Integer, ByVal PageSizetemp As Integer, ByVal countint As Integer, ByVal pagename As String) As String

Dim i As Integer

If countint Mod PageSizetemp = 0 Then

i = countint \ PageSizetemp

Else

i = countint \ PageSizetemp + 1

End If

Dim maxi, mini As Integer

Dim navleft, navright, navstrtemp As String

If i 10 Then

maxi = i

mini = 1

Else

maxi = pageindex + 3

mini = pageindex - 3

If mini 1 Then

navleft = "a href=""" pagename "?page=" (mini - 1) """ class=""link_nav_btn""/a "

Else

mini = 1

maxi = 10

End If

If maxi i Then

navright = " a href=""" pagename "?page=" (maxi + 1) """ class=""link_nav_btn""/a"

Else

If i - 10 0 Then

mini = i - 10

Else

mini = 1

End If

maxi = i

End If

End If

For n As Integer = mini To maxi

If n = pageindex Then

navstrtemp = navstrtemp " a href=""" pagename "?page=" n """ class=""link_nav_btn_select""b" n "/b/a"

Else

navstrtemp = navstrtemp " a href=""" pagename "?page=" n """ class=""link_nav_btn""" n "/a"

End If

Next

navstrtemp = navleft navstrtemp navright

Return navstrtemp

End Function

Sub databinds(ByVal tblnametemp As String, ByVal strGetFieldstemp As String, ByVal fldNametemp As String, ByVal PageSizetemp As Integer, ByVal PageIndextemp As Integer, ByVal OrderTypetemp As Short, ByVal strWheretemp As String)

'tblnametemp表名,strGetFieldstemp需要返回的列,fldNametemp排序的字段名,PageSizetemp页尺寸,PageIndextemp页码,OrderTypetemp设置排序类型,strWheretemp查询条件

'总数

cmdTM = New SqlCommand("select count(*) from " tblnametemp " where " strWheretemp, conPubs)

conPubs.Open()

countint = CInt(cmdTM.ExecuteScalar())

conPubs.Close()

'导航

navstr = navindex(PageIndextemp, PageSizetemp, countint, "newshyxh.aspx")

'分页

cmdTM = New SqlCommand("Pagination", conPubs)

cmdTM.CommandType = CommandType.StoredProcedure

'add input

cmdTM.Parameters.Add("@tblName", SqlDbType.VarChar, 255).Value = tblnametemp

cmdTM.Parameters.Add("@strGetFields", SqlDbType.VarChar, 1000).Value = strGetFieldstemp

cmdTM.Parameters.Add("@fldName", SqlDbType.VarChar, 255).Value = fldNametemp

cmdTM.Parameters.Add("@PageIndex", SqlDbType.Int).Value = PageIndextemp

cmdTM.Parameters.Add("@PageSize", SqlDbType.Int).Value = PageSizetemp

cmdTM.Parameters.Add("@OrderType", SqlDbType.Bit).Value = OrderTypetemp

cmdTM.Parameters.Add("@strWhere", SqlDbType.VarChar, 1500).Value = strWheretemp

conPubs.Open()

newsright.DataSource = cmdTM.ExecuteReader()

newsright.DataBind()

conPubs.Close()

End Sub