CREATE TABLE [dbo].[table1](
[ID] [int] IDENTITY(1,1) NOT NULL,
[GFNAME] [varchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL,
[GFTEST] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[GFGOOD] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[CREATEDATE] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL
)
假如里面七十条数据,做个简单的sql查询分页,用sql语句
[ID] [int] IDENTITY(1,1) NOT NULL,
[GFNAME] [varchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL,
[GFTEST] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[GFGOOD] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[CREATEDATE] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL
)
假如里面七十条数据,做个简单的sql查询分页,用sql语句
declare @iPageNo int,@iPageCount int
set @iPageNo=3 --页数
set @iPageCount = 20 select * from 表 where ID<= @iPageNo * @iPageCount and ID> (@iPageNo - 1) * @iPageCount
用ID作为分页的,你只能确保你的ID是连续性的,不能中断,中断后数据库可能就不是你想要的了
SET @pagenum = 2
SET @pagesize = 3
SET @P=@pagenum*(@pagesize-1)
SELECT TOP (@pagesize) *
FROM table1
WHERE id NOT IN
(
SELECT TOP (@p) id FROM table1 ORDER BY id
)
ORDER BY id
SET @pagenum = 2
SET @pagesize = 3
SELECT *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY id DESC) AS rownum,
*
FROM TABLE1) AS D
WHERE rownum BETWEEN (@pagenum-1)*@pagesize+1 AND @pagenum*@pagesize
ORDER BY id DESC
create proc sp_fenye
@page int
as
declare @sql varchar(1000)
set @sql='select top 10 * from table1 where id not in(select top '+ltrim(@page-1)*10+' id from table1 order by id) order by id'
exec(@sql)
go--调用:
exec sp_fenye @page=1
--sql2000
create proc sp_fenye
@page int
as
declare @sql varchar(1000)
set @sql='select top 10 * from table1 where id not in(select top '+ltrim((@page-1)*10)+' id from table1 order by id) order by id'
exec(@sql)
go--sql2005
create proc sp_fenye
@page int
as
select top 10 * from
(select xh=row_Number() over(order by id) from table1)a
where xh between (@page-1)*10+1 and @page*10
go
--调用
exec sp_fenye @page=1
{
stirng sql= string.Empty;
if(page==1)
{
sql=string.format("select top 15 * from table")
}else
{
page=(page-1)*15
sql=string.Format("select top 15 * from table where ID not in(select Top {0} ID from )",page.ToString())
}
}
static void Find( int page)
{
stirng sql= string.Empty;
if(page==1)
{
sql=string.format("select top 15 * from table")
}else
{
page=(page-1)*15
sql=string.Format("select top 15 * from table where ID not in(select Top {0} ID from )",page.ToString())
}
}