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 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
}
我写的,感觉通不过...
解决方案 »
- C# 枚举参数的问题
- C++/CLI 不能调C# class 的 Dispose()方法
- FCKeditor怎么提取图片地址
- 如何给生成的PDF文件加入书签
- (顶的有分)求助!c#怎莫使用Visual Studio 2005 Extensions for Windows Workflow Foundation ?
- 问关于 VC# 编译器的代码优化的问题,还有 VC# 做图象处理时性能怎么样。
- 从sql2005 导出image 格式的数据表 怎么把它插入到excel中
- 怎样获取script写的模板中的值
- 多线程命名的问题,在线等待
- 请问我准备开始学习C#(有C/C++的编程基础)!应该从何入手?有那些好的书籍?
- treeview 使用的问题
- 后台运行一个外部程序,现在前台鼠标始终显示该程序运行的状态,如何取消
(
select top (pageSize*(currPage-1)) id from product where id in
(
select max(id) from product group by Name
)
)
(
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
不行...提示什么错误,或者说你是要实现怎么样的分页
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
}
把它当做一个临时表什么的,然后分页它, 这样可以吧。。 怎么搞
create table #tmp
(
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 --每页显示数
ASDECLARE @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 OUTPUTIF (@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)
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GOCREATE 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 --每页显示多少行
ASDECLARE @SelectString NVARCHAR(1000)--动态sql语句
DECLARE @TotalRow INT --总共有多少行
DECLARE @TotalPage INT --总共有多少页SET NOCOUNT ONSET @SelectString = 'SELECT @TotalRow=count(*) FROM '+@TableName
IF @SelectWhere != ''
BEGIN
SET @SelectString = @SelectString+' WHERE '+@SelectWhere ;
ENDEXECUTE sp_EXECUTESQL @SelectString,N'@TotalRow int output',@TotalRow output SET @TotalPage = @TotalRow/@PageSize
IF (@TotalRow%@PageSize) != 0
BEGIN
SET @TotalPage = @TotalPage + 1;
ENDSELECT @TotalRow AS TotalRow,@PageSize AS PageSize,@TotalPage AS TotalPage ,@PageNo AS PageNoIF @SelectWhere !=''
BEGIN
SET @SelectWhere = ' WHERE '+@SelectWhere
ENDSET @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 @SelectStringSELECT @SelectStringSET NOCOUNT OFFRETURN (@@rowcount)