SELECT OP_OrderConfirm.Fast_Flag, OP_OrderConfirm.OS_NO, MF_PSS.PS_NO,
MF_PSS.PS_DD, TF_PSS.PRD_NO, TF_PSS.PRD_NAME, TF_PSS.QTY,
TF_PSS.UP, TF_PSS.AMT, BG_CUST.CustName, MF_PSS.PS_ID, MF_POS.USR,
BG_Employee.Name
FROM OP_OrderConfirm INNER JOIN
MF_PSS ON OP_OrderConfirm.OS_NO = MF_PSS.OS_NO INNER JOIN
TF_PSS ON MF_PSS.PS_NO = TF_PSS.PS_NO INNER JOIN
BG_CUST ON MF_PSS.CUS_NO = BG_CUST.CustNumber INNER JOIN
MF_POS ON OP_OrderConfirm.OS_NO = MF_POS.OS_NO LEFT OUTER JOIN
BG_Employee ON MF_POS.USR = BG_Employee.Num
解决方案 »
- 需求分析怎么做
- Gridview编辑状态无法取消,并且出现”未能加载视图状态。正在向其中加载视图状态的控件树必须与前一请求期间用于保存视图状态的控件树相匹配“错误
- Forms验证问题。急。。。
- asp.net调用ffmpeg.exe
- 关于梅花雪树的两个问题!
- 请教这个方案该如何做?
- 一个AJAX的ModalPopupExtender控件的实例
- 还是关于toolbar里的dropdownlist取值的问题
- session传值问题求教
- 怎么在一个自定义类中调用另一个自定义类中的函数?
- 【求助】asp.net的web.cofig里面怎么连接已配置好的DSN?
- FrameSet中控制控件大小问题?????
(
@PageSize int, -------------------页大小
@pageIndex int, -------------------当前页码
@pageCount int output, -------------------输出总页数
@counts int output -------------------查输出总记录数
)
asselect * into #temp from (SELECT OP_OrderConfirm.Fast_Flag, OP_OrderConfirm.OS_NO, MF_PSS.PS_NO,
MF_PSS.PS_DD, TF_PSS.PRD_NO, TF_PSS.PRD_NAME, TF_PSS.QTY,
TF_PSS.UP, TF_PSS.AMT, BG_CUST.CustName, MF_PSS.PS_ID, MF_POS.USR,
BG_Employee.Name
FROM OP_OrderConfirm INNER JOIN
MF_PSS ON OP_OrderConfirm.OS_NO = MF_PSS.OS_NO INNER JOIN
TF_PSS ON MF_PSS.PS_NO = TF_PSS.PS_NO INNER JOIN
BG_CUST ON MF_PSS.CUS_NO = BG_CUST.CustNumber INNER JOIN
MF_POS ON OP_OrderConfirm.OS_NO = MF_POS.OS_NO LEFT OUTER JOIN
BG_Employee ON MF_POS.USR = BG_Employee.Num) as adeclare @strSql nvarchar(4000)
set @strSql=N'select @counts=count(*) from #temp'exec sp_executesql @strSql, N' @counts int output', @counts outputif(@counts=0)
select @pageCount=1
else
select @pageCount=(@Counts+@pageSize-1)/@pageSize
if @pageIndex =1
begin
set @strSql=N'select distinct top '+rtrim(@pageSize)+N' * from #temp oder by (你的标识列,我一般用ID)'
end
else
begin
set @strSql=N'select distinct top '+rtrim(@pageSize)+N' * from #temp '
set @strSql=@strSql+N' where (标识列)>('
set @strSql=@strSql+N'select max(BugID) from ('
set @strSql=@strSql+N'select top '+rtrim((@pageIndex-1)*@pageSize)+N' a.BugID from #temp order by (标识列)) as t) order by (标识列) ' endexec(@strSql)
drop table #temp根据你自己的需要在上面修改就应该可以了吧
GO
SET ANSI_NULLS ON
GO
CREATE proc GetThePage @pagesize int,@pageindex int,@col nvarchar(100),@tb nvarchar(20),@wherestr nvarchar(100),@sOrder varchar(10),@sOrdStr nvarchar(20) AS--@pagesize 分页设置大小
--@pageindex 当前页面索引值
--@col 列名 'a,b,c...'
--@tb 表名
--@wherestr 'where 1=1 and .....'条件语句
--@sOrder //'asc' 或者 'desc'
--@sOrdStr 排序字段 DECLARE @sSQL NVARCHAR(4000)if @sOrder = 'asc' set @sSQL = 'select '+@col+' from (select top ' + cast(@pagesize as nvarchar) +' '+@col+' from ( select top '+cast((@pagesize * @pageindex + @pagesize) as nvarchar)+' '+@col+' from '+ @tb +' '+ @wherestr +' order by '+ @sOrdStr +' asc ) tt '+ @wherestr +' order by '+ @sOrdStr +' desc) tp '+@wherestr+' order by '+ @sOrdStr +' asc'
else set @sSQL = 'select '+@col+' from (select top ' + cast(@pagesize as nvarchar) +' '+@col+' from ( select top '+cast((@pagesize * @pageindex + @pagesize) as nvarchar)+' '+@col+' from '+ @tb + ' ' + @wherestr + ' order by '+ @sOrdStr +' desc ) tt '+ @wherestr +' order by '+ @sOrdStr +' asc) tp '+@wherestr+' order by '+ @sOrdStr +' desc'
EXEC(@sSQL)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO