SQL2008多个查询条件分页查询脚本(原创)
作者:C/S框架网  发布日期:2018-07-22 20:52:05
  SQL2008多个查询条件分页查询脚本(原创)



SQL Code:

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
ALTER PROCEDURE [dbo].[usp_Wallet_GetTransList]
@Account VARCHAR(50),
@D1 DATETIME,--起始时间
@D2 DATETIME,--介绍时间
@DocType INT=0,--1充值,2提现
@OrderID VARCHAR(50)='',--订单编号
@PayType VARCHAR(10)='',--wx, alipay, tt
@State INT=0, --QT:查询类型(0:全部, 1:未入账, 2:已入账)
@PageNo INT=1,--当前页码
@PageSize INT=10--没页记录数
AS
BEGIN

/****************************************************************

程序说明:分页查询交易历史记录
作者:孙中吕

SELECT * FROM tb_Trans ORDER BY CreateTime DESC;

usp_Wallet_GetTransList '1308021008307676AF','1901-01-01','2019-01-01',1,'','',0,1,10
usp_Wallet_GetTransList '1308021008307676AF','1901-01-01','2019-01-01',1,'aa','wx',0,3,5

****************************************************************/

--兼容性检查
IF @PageNo<=0 SET @PageNo=1;
IF @PageSize<=0 SET @PageSize=10;

--计算起始序号和截止序号
DECLARE @StartIndex INT,@EndIndex INT
SELECT @StartIndex=(@PageNo-1)*@PageSize+1;
SELECT @EndIndex=@PageNo*@PageSize;

--临时表
CREATE TABLE #isid(isid INT)
CREATE INDEX isidIndex ON #isid(isid)

DECLARE @SQL VARCHAR(max)

--必选条件
SET @SQL='INSERT INTO #ISID SELECT isid FROM dbo.tb_Trans WHERE Account='''+@Account+''' '
SET @SQL=@SQL+' AND CONVERT(VARCHAR,CreateTime,112) BETWEEN '''+CONVERT(VARCHAR,@D1,112) +''' AND '''+CONVERT(VARCHAR,@D2,112)+''' '

--可选条件
IF @DocType>0 SET @SQL=@SQL+' AND DocType='+CAST(@DocType AS VARCHAR)
IF ISNULL(@OrderID,'')<>'' SET @SQL=@SQL+' AND OrderID='''+@OrderID+''' '
IF ISNULL(@PayType,'')<>'' SET @SQL=@SQL+' AND PayType='''+@PayType+''' '
IF @State>0 SET @SQL=@SQL+' AND State='+CAST(@State AS VARCHAR)
--PRINT @SQL
EXEC(@SQL);

--返回结果,注意:按创建日期倒序排列
WITH T AS
(
SELECT *,ROW_NUMBER() OVER(ORDER BY CreateTime DESC) AS rowNum FROM dbo.tb_Trans
WHERE isid IN (SELECT isid FROM #isid)
)
SELECT * FROM T WHERE rowNum BETWEEN @StartIndex AND @EndIndex ORDER BY CreateTime DESC

--SELECT TOP 100 * FROM dbo.tb_Trans ORDER BY CreateTime DESC
END;
GO


//来源:C/S框架网(www.csframework.com) QQ:23404761



上一篇 下一篇