求解sql 分页
select * from product where id in
{
select max(id) from product group by Name
}
这句是已经出来的结果，需要把它（结果）分页
select top pageSize from where id in
{
select top (pageSize*(currPage-1)) from product where id in
{
select max(id) from product group by Name
} as table1
}
我写的，感觉通不过... --------------------编程问答--------------------
select top pageSize from product where id in--------------------编程问答--------------------
(
select top (pageSize*(currPage-1)) id from product where id in
(
select max(id) from product group by Name
)
)
select top pageSize from product where id in--------------------编程问答--------------------
(
select top (pageSize*(currPage-1)) id from product where id in
(
select max(id) from product group by Name
)
)
不行... --------------------编程问答--------------------
提示什么错误，或者说你是要实现怎么样的分页
select top pageSize id from product where id in
(
select top (pageSize*(currPage-1)) id from product where id in
(
select max(id) from product group by Name
)
)
发现少了个id --------------------编程问答--------------------
还是不行，我需要所有数据。 不只是id
--------------------编程问答--------------------
select * from product where id in
{
select max(id) from product group by Name
}
把它当做一个临时表什么的，然后分页它， 这样可以吧。。 怎么搞 --------------------编程问答-------------------- 把id换成*就能查全部了
临时表
create table #tmp--------------------编程问答-------------------- 参考 --------------------编程问答-------------------- 用 Linq的Skip（）方法。 --------------------编程问答-------------------- http://www.usingdll.com/6692.html --------------------编程问答-------------------- ++ --------------------编程问答-------------------- ----------------------------适用于MSSQL2000------------------------------------
(
id int,
name varchar(50)
)
insert into #tmp(id,name) values(select id,name from product)
select * from #tmp
drop table #tmp
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[getdataset]
@TotalCount INT OUTPUT, --总记录数(存储过程输出参数)
@TableName NVARCHAR(1000), --搜索的表名
@MainField NVARCHAR(100), --表的主要排序字段,如主键等,也可以是其他字段
@FieldList NVARCHAR(1000)='*', --搜索表的字段,比如：’id，datatime，job‘，用逗号隔开
@SelectWhere NVARCHAR(1000)='', --搜索条件，这里不用写where，比如：job=’teacher‘ and class='2'
@OrderField NVARCHAR(1000)='', --排序，可以使用多字段排序但主键字段必需在最前面.也可以不写，比如：order by class asc
@IntPageNo INT=1, --页号
@IntPageSize INT=10 --每页显示数
AS
DECLARE @SelectString NVARCHAR(1000)
SET NOCOUNT ON --关闭计数
SET @SelectString = 'SELECT @TotalCount = count(*) FROM '+@TableName
IF @SelectWhere != ''
BEGIN
SET @SelectString = @SelectString+' WHERE '+@SelectWhere ;
END
EXECUTE sp_EXECUTESQL @SelectString,N'@TotalCount INT OUTPUT',@TotalCount OUTPUT
IF (@TotalCount = 0) --如果没有记录，则返回零
return 0
IF (@IntPageNo - 1) * @IntPageSize > @TotalCount --判断页数是否正确,如果页号大于总页数,返回错误
return (-1)
SET NOCOUNT OFF --打开计数
IF @SelectWhere != ''
BEGIN
SET @SelectString = 'select top '+str(@IntPageSize)+' '+@FieldList+' from '+@TableName+' where '+@MainField+' not in(select top '+str((@IntPageNo-1)*@IntPageSize)+' '+@MainField+' from '+@TableName+' '+@SelectWhere +' '+@OrderField+') and '+@SelectWhere +' '+@OrderField
END
ELSE
BEGIN
SET @SelectString = 'select top '+str(@IntPageSize)+' '+@FieldList+' from '+@TableName+' where '+@MainField+' not in(select top '+str((@IntPageNo-1)*@IntPageSize)+' '+@MainField+' from '+@TableName+' '+@OrderField+') '+@OrderField
END
EXECUTE sp_EXECUTESQL @SelectString
SELECT (@@rowcount)
--------------------编程问答-------------------- ------------------------------适用MSSQL2005以上----------------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[st_PaginationBase]
@TableName NVARCHAR(1000), --表名,可以是联合表.
@OrderField NVARCHAR(1000), --排序字段,至少要指定一个字段
@FieldList NVARCHAR(1000)='*', --表的字段,比如：'id,datatime,job',用逗号隔开
@SelectWhere NVARCHAR(1000)='', --条件，不用写where，比如：job='teacher' and class='2'
@PageNo INT=1, --当前页号
@PageSize INT=10 --每页显示多少行
AS
DECLARE @SelectString NVARCHAR(1000)--动态sql语句
DECLARE @TotalRow INT --总共有多少行
DECLARE @TotalPage INT --总共有多少页
SET NOCOUNT ON
SET @SelectString = 'SELECT @TotalRow=count(*) FROM '+@TableName
IF @SelectWhere != ''
BEGIN
SET @SelectString = @SelectString+' WHERE '+@SelectWhere ;
END
EXECUTE sp_EXECUTESQL @SelectString,N'@TotalRow int output',@TotalRow output
SET @TotalPage = @TotalRow/@PageSize
IF (@TotalRow%@PageSize) != 0
BEGIN
SET @TotalPage = @TotalPage + 1;
END
SELECT @TotalRow AS TotalRow,@PageSize AS PageSize,@TotalPage AS TotalPage ,@PageNo AS PageNo
IF @SelectWhere !=''
BEGIN
SET @SelectWhere = ' WHERE '+@SelectWhere
END
SET @SelectString=
'SELECT '+@FieldList+' FROM ('+
'SELECT ROW_NUMBER() OVER(ORDER BY '+@OrderField+') AS ROWNUMBER, '+@FieldList+ ' FROM '+@TableName+@SelectWhere+
') AS TempTable WHERE ROWNUMBER BETWEEN '+str(((@PageNo - 1) * @PageSize)+1)+' AND '+str((@PageNo * @PageSize))
EXECUTE sp_EXECUTESQL @SelectString
SELECT @SelectString
SET NOCOUNT OFF
RETURN (@@rowcount)
--------------------编程问答-------------------- 分页这事不是都做烂大街了么？
补充：.NET技术 , C#
