[color=#000000][/color]ALTER PROC [dbo].[Proc_GetCardSaledRecord]
@PageSize int=5 ,--每页显示条数
@CurrentPageIndex int,--当前页索引
@WhereSql VARCHAR(1000)='' --查询条件
AS
BEGIN
CREATE TABLE #T_CardSaledRecord
(
ID INT,
Type INT,
Consumption VARCHAR(50),
StartSerialNumber INT,
EndSerialNumber INT,
SaledNum INT,
Receiptor VARCHAR(20),
Ausstehend INT,
PaidUp INT,
Validity INT,
UseCount INT,
CardOpenValidity DATETIME,
Creater VARCHAR(50),
CreatTime DATETIME,
OpenCount INT,
Opened INT,
UsedCount INT,
Used INT,
OpenUtilization FLOAT,
UseUtilization FLOAT
)
DECLARE @Type INT,@StartSerialNumber INT,@EndSerialNumber INT,@OpenCount INT,@Opened INT,@Used INT,@UsedCount INT,@ID INT
DECLARE @Consumption VARCHAR(50),@ConsumptionID INT,@SaledNum INT,@Receiptor VARCHAR(20),@Ausstehend INT,@PaidUp INT,@Validity INT,@UseCount INT,@CardOpenValidity DATETIME
DECLARE @Creater VARCHAR(50),@CreatTime DATETIME
DECLARE CardSaledRecord_Cur CURSOR
FOR
SELECT ID,Type,ConsumptionID,StartSerialNumber,EndSerialNumber,SaledNum,Receiptor,Ausstehend,PaidUp,Validity,UseCount,CardOpenValidity,Creater,CreatTime FROM CardSaledRecord
OPEN CardSaledRecord_Cur
FETCH NEXT FROM CardSaledRecord_Cur INTO @ID,@Type,@ConsumptionID,@StartSerialNumber,@EndSerialNumber,@SaledNum,@Receiptor,@Ausstehend,@PaidUp,@Validity,@UseCount,@CardOpenValidity,@Creater,@CreatTime
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @OpenCount=COUNT(1),@UsedCount=SUM(TotalCount),@Used=SUM(useCount) FROM HM_CardDetail WHERE CardType=@Type AND (SerialNumber BETWEEN @StartSerialNumber AND @EndSerialNumber)
SELECT @Opened=COUNT(1) FROM HM_CardDetail WHERE CardType=@Type AND ISNULL(Openuserid,'')<>'' AND (SerialNumber BETWEEN @StartSerialNumber AND @EndSerialNumber)
SELECT @Consumption=Company FROM Consumption WHERE ID=@ConsumptionID
IF @OpenCount=0
INSERT INTO #T_CardSaledRecord VALUES(@ID,@Type,@Consumption,@StartSerialNumber,@EndSerialNumber,@SaledNum,@Receiptor,@Ausstehend,@PaidUp,@Validity,@UseCount,@CardOpenValidity,@Creater,@CreatTime,@OpenCount,@Opened,@UsedCount,@UsedCount-@Used,0,(@UsedCount-@Used)*100/@UsedCount)
ELSE
INSERT INTO #T_CardSaledRecord VALUES(@ID,@Type,@Consumption,@StartSerialNumber,@EndSerialNumber,@SaledNum,@Receiptor,@Ausstehend,@PaidUp,@Validity,@UseCount,@CardOpenValidity,@Creater,@CreatTime,@OpenCount,@Opened,@UsedCount,@UsedCount-@Used,@Opened*100/@OpenCount,(@UsedCount-@Used)*100/@UsedCount)
FETCH NEXT FROM CardSaledRecord_Cur INTO @ID,@Type,@ConsumptionID,@StartSerialNumber,@EndSerialNumber,@SaledNum,@Receiptor,@Ausstehend,@PaidUp,@Validity,@UseCount,@CardOpenValidity,@Creater,@CreatTime
END
CLOSE CardSaledRecord_Cur
DEALLOCATE CardSaledRecord_Cur
IF @WhereSql=''
begin
--@PageSize int ,--每页显示条数
--@CurrentPageIndex int,--当前页索引 从0开始.
WITH tt AS
(
SELECT *,ROW_NUMBER() OVER(ORDER BY ID) AS pid FROM #T_CardSaledRecord
)
SELECT * FROM tt
WHERE pid > (@CurrentPageIndex-1)*@PageSize AND pid <=@CurrentPageIndex*@PageSize;
end
ELSE
BEGIN
DECLARE @Sql VARCHAR(1000)
SET @Sql='SELECT * FROM #T_CardSaledRecord WHERE 1=1 '+@WhereSql;
SELECT * INTO #T_CardSaledRecord2 FROM #T_CardSaledRecord WHERE 1=2;
INSERT INTO #T_CardSaledRecord2 EXEC(@Sql);
WITH ttt AS
(
SELECT *,ROW_NUMBER() OVER(ORDER BY ID) AS pid FROM #T_CardSaledRecord2
)
SELECT * FROM ttt
WHERE pid > (@CurrentPageIndex-1)*@PageSize AND pid <=@CurrentPageIndex*@PageSize;
END
end
这个是一个3表连接带参数分页，我想获取他的总页数和按时间排序以及首页和尾页的判断
------解决方案--------------------------------------------------------
- SQL code
ALTER PROC [dbo].[Proc_GetCardSaledRecord]@PageSize int=5 ,--每页显示条数@CurrentPageIndex int,--当前页索引@WhereSql VARCHAR(1000)='', --查询条件@Order bit=0,--排序方式 0顺序 1 逆序 @Pagecount int out --总页数ASBEGINCREATE TABLE #T_CardSaledRecord(ID INT,Type INT,Consumption VARCHAR(50),StartSerialNumber INT,EndSerialNumber INT,SaledNum INT,Receiptor VARCHAR(20),Ausstehend INT,PaidUp INT,Validity INT,UseCount INT,CardOpenValidity DATETIME,Creater VARCHAR(50),CreatTime DATETIME,OpenCount INT,Opened INT,UsedCount INT,Used INT,OpenUtilization FLOAT,UseUtilization FLOAT)DECLARE @Type INT,@StartSerialNumber INT,@EndSerialNumber INT,@OpenCount INT,@Opened INT,@Used INT,@UsedCount INT,@ID INTDECLARE @Consumption VARCHAR(50),@ConsumptionID INT,@SaledNum INT,@Receiptor VARCHAR(20),@Ausstehend INT,@PaidUp INT,@Validity INT,@UseCount INT,@CardOpenValidity DATETIMEDECLARE @Creater VARCHAR(50),@CreatTime DATETIMEDECLARE CardSaledRecord_Cur CURSORFORSELECT ID,Type,ConsumptionID,StartSerialNumber,EndSerialNumber,SaledNum,Receiptor,Ausstehend,PaidUp,Validity,UseCount,CardOpenValidity,Creater,CreatTime FROM CardSaledRecordOPEN CardSaledRecord_CurFETCH NEXT FROM CardSaledRecord_Cur INTO @ID,@Type,@ConsumptionID,@StartSerialNumber,@EndSerialNumber,@SaledNum,@Receiptor,@Ausstehend,@PaidUp,@Validity,@UseCount,@CardOpenValidity,@Creater,@CreatTimeWHILE @@FETCH_STATUS=0BEGINSELECT @OpenCount=COUNT(1),@UsedCount=SUM(TotalCount),@Used=SUM(useCount) FROM HM_CardDetail WHERE CardType=@Type AND (SerialNumber BETWEEN @StartSerialNumber AND @EndSerialNumber)SELECT @Opened=COUNT(1) FROM HM_CardDetail WHERE CardType=@Type AND ISNULL(Openuserid,'')<>'' AND (SerialNumber BETWEEN @StartSerialNumber AND @EndSerialNumber)SELECT @Consumption=Company FROM Consumption WHERE ID=@ConsumptionIDIF @OpenCount=0INSERT INTO #T_CardSaledRecord VALUES(@ID,@Type,@Consumption,@StartSerialNumber,@EndSerialNumber,@SaledNum,@Receiptor,@Ausstehend,@PaidUp,@Validity,@UseCount,@CardOpenValidity,@Creater,@CreatTime,@OpenCount,@Opened,@UsedCount,@UsedCount-@Used,0,(@UsedCount-@Used)*100/@UsedCount)ELSEINSERT INTO #T_CardSaledRecord VALUES(@ID,@Type,@Consumption,@StartSerialNumber,@EndSerialNumber,@SaledNum,@Receiptor,@Ausstehend,@PaidUp,@Validity,@UseCount,@CardOpenValidity,@Creater,@CreatTime,@OpenCount,@Opened,@UsedCount,@UsedCount-@Used,@Opened*100/@OpenCount,(@UsedCount-@Used)*100/@UsedCount)FETCH NEXT FROM CardSaledRecord_Cur INTO @ID,@Type,@ConsumptionID,@StartSerialNumber,@EndSerialNumber,@SaledNum,@Receiptor,@Ausstehend,@PaidUp,@Validity,@UseCount,@CardOpenValidity,@Creater,@CreatTimeENDCLOSE CardSaledRecord_CurDEALLOCATE CardSaledRecord_CurIF @WhereSql=''begin--@PageSize int ,--每页显示条数--@CurrentPageIndex int,--当前页索引 从0开始.WITH tt AS(SELECT *,ROW_NUMBER() OVER(ORDER BY case @order when 0 then cast(CreatTime as int) else cast(CreatTime as int)*-1 end) AS pid FROM #T_CardSaledRecord )SELECT * FROM ttWHERE pid > (@CurrentPageIndex-1)*@PageSize AND pid <=@CurrentPageIndex*@PageSize;set @Pagecount=@@ROWCOUNTendELSEBEGINDECLARE @Sql VARCHAR(1000)SET @Sql='SELECT * FROM #T_CardSaledRecord WHERE 1=1 '+@WhereSql;SELECT * INTO #T_CardSaledRecord2 FROM #T_CardSaledRecord WHERE 1=2; INSERT INTO #T_CardSaledRecord2 EXEC(@Sql); WITH ttt AS(SELECT *,ROW_NUMBER() OVER(ORDER BY case @order when 0 then cast(CreatTime as int) else cast(CreatTime as int)*-1 end) AS pid FROM #T_CardSaledRecord2 )SELECT * FROM tttWHERE pid > (@CurrentPageIndex-1)*@PageSize AND pid <=@CurrentPageIndex*@PageSize;set @Pagecount=@@ROWCOUNTENDend