select * from (select *,ROW_NUMBER() over (order by No) as id from TB_PropCustomBasicData) as tmp where 1=1 and No='2003' and tmp.id between 0+1 and 8 ----debug是这样子的,只能查询到当前第一页的数据,第二页第三页木有,由于受到ROW_NUMBER() 分页的限制
select * from (select *,ROW_NUMBER() over (order by No) as id from TB_PropCustomBasicData) as tmp where 1=1 and No='2003' and tmp.id between 0+1 and 8 ----debug是这样子的,只能查询到当前第一页的数据,第二页第三页木有,由于受到ROW_NUMBER() 分页的限制偶想做滴就是把查询的所有结果都查出来,结果结果的分页,难道我表达有问题,大神??!!
那该如何做才能好一点了,大神,谢谢 大概这样 --写个存储过程,返回两个结果集 --1、 DECLARE @name VARCHAR(200) ,@PageIndex INT ,@PageSize INT--传入参数 DECLARE @StartRN INT , @EndRn INT , @MaxCount INT , @PageCount INT
SELECT @MaxCount=COUNT(*) FROM from TB_PropCustomBasicData WHERE name=@name
IF @PageSize=0 SET @PageCount=1 ELSE SET @PageCount = ((@MaxCount -1) / @PageSize) + 1; --页数
SELECT @MaxCount AS MaxCount ,@PageCount AS PageCount ---结果集一,得到总数和总页数,easyUI可以不用页数
SET @StartRN=(@PageIndex - 1) * @PageSize + 1 --计算起始rn SET @EndRn=@PageIndex*@PageSize
select * from ( select *,ROW_NUMBER() over (order by No) as id from TB_PropCustomBasicData where 1=1 and name = 'xxx' ) as tmp WHERE tmp.id between @StartRN AND @EndRn 页面上设置好参数easyUI 上百度 $('#tt').datagrid({ url: 'admin/..../xxx', width: 900, //height: 350, title: '列表', //iconCls: 'icon-save', fitColumns: true, rownumbers: true, //是否加行号 pagination: true, //是否显式分页 pageSize: 10, //页容量,必须和pageList对应起来,否则会报错 pageNumber: 1, //默认显示第几页 autoRowHeight: true,//自适应高度 nowrap: false,// 如果为true,则在同一行中显示数据。设置为true可以提高加载性能。 pageList: [10, 20, 30],//分页中下拉选项的数值 columns: [[ 请求到数据JSON返回 var jsonresult = new { total = maxCount, rows = list }; return Json(jsonresult, JsonRequestBehavior.AllowGet);
那该如何做才能好一点了,大神,谢谢按照你的思路将sql语句改成这样子就ok了select * from (select *,ROW_NUMBER() over (order by No) as id from TB_PropCustomBasicData where 1=1 and No='2003' ) ) as tmp where tmp.id between 0+1 and 8,感谢大神。sql不过关哪
select * from (select *,ROW_NUMBER() over (order by No) as id from TB_PropCustomBasicData) as tmp where 1=1 and No='2003' and tmp.id between 0+1 and 8 ----debug是这样子的,只能查询到当前第一页的数据,第二页第三页木有,由于受到ROW_NUMBER() 分页的限制
是要获取所有数据吗,如果不改SQL,直接传入很大的页码就能带出来了
select * from (select *,ROW_NUMBER() over (order by No) as id from TB_PropCustomBasicData) as tmp where 1=1 and No='2003' and tmp.id between 0+1 and 8 ----debug是这样子的,只能查询到当前第一页的数据,第二页第三页木有,由于受到ROW_NUMBER() 分页的限制偶想做滴就是把查询的所有结果都查出来,结果结果的分页,难道我表达有问题,大神??!!
那该如何做才能好一点了,大神,谢谢
大概这样
--写个存储过程,返回两个结果集
--1、
DECLARE @name VARCHAR(200)
,@PageIndex INT
,@PageSize INT--传入参数
DECLARE @StartRN INT
, @EndRn INT
, @MaxCount INT
, @PageCount INT
SELECT @MaxCount=COUNT(*) FROM from TB_PropCustomBasicData WHERE name=@name
IF @PageSize=0
SET @PageCount=1
ELSE
SET @PageCount = ((@MaxCount -1) / @PageSize) + 1; --页数
SELECT @MaxCount AS MaxCount ,@PageCount AS PageCount ---结果集一,得到总数和总页数,easyUI可以不用页数
SET @StartRN=(@PageIndex - 1) * @PageSize + 1 --计算起始rn
SET @EndRn=@PageIndex*@PageSize
select *
from (
select *,ROW_NUMBER() over (order by No) as id
from TB_PropCustomBasicData
where 1=1
and name = 'xxx'
) as tmp
WHERE tmp.id between @StartRN AND @EndRn
页面上设置好参数easyUI 上百度
$('#tt').datagrid({
url: 'admin/..../xxx',
width: 900,
//height: 350,
title: '列表',
//iconCls: 'icon-save',
fitColumns: true,
rownumbers: true, //是否加行号
pagination: true, //是否显式分页
pageSize: 10, //页容量,必须和pageList对应起来,否则会报错
pageNumber: 1, //默认显示第几页
autoRowHeight: true,//自适应高度
nowrap: false,// 如果为true,则在同一行中显示数据。设置为true可以提高加载性能。
pageList: [10, 20, 30],//分页中下拉选项的数值
columns: [[
请求到数据JSON返回 var jsonresult = new { total = maxCount, rows = list }; return Json(jsonresult, JsonRequestBehavior.AllowGet);
那该如何做才能好一点了,大神,谢谢按照你的思路将sql语句改成这样子就ok了select * from (select *,ROW_NUMBER() over (order by No) as id from TB_PropCustomBasicData where 1=1 and No='2003' ) ) as tmp where tmp.id between 0+1 and 8,感谢大神。sql不过关哪