分页存储过程(五)在MS SQL Server中打造更加准确,且有一点效率提升的的分页结果
2010-06-08 08:38 Virus-BeautyCode 阅读(1734) 评论(2) 编辑 收藏 举报以前的分页我都是主子表join查询,然后会出现下面的记录形式,返回时一个数据集合
订单1 明细1.1
订单1 明细1.2
订单1 明细1.3
订单2 明细2.1
订单2 明细2.2
订单1 明细1.2
订单1 明细1.3
订单2 明细2.1
订单2 明细2.2
这样的话,就造成大量的数据冗余,就是【订单1】的数据被重复多次从数据库读取。这次我尝试将他们分开返回,返回两个数据集合
订单集合
订单1
订单2
订单3
订单2
订单3
明细集合
明细1.1
明细1.2
明细1.3
明细2.1
明细2.2
明细1.2
明细1.3
明细2.1
明细2.2
这样的结果不知道会不会有效率提升,还有待测试。但是数据量少了,联合的数据量也少了。以前需要联合整张订单表和整张明细表,现在首先将符合条件的订单找到,然后用符合条件的订单和明细表联合,直接查找符合条件的订单的明细,目标明确了。
当然了,找到的符合条件的订单还是放在零时表中,然后用临时表和明细表进行inner join查询明细信息。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
DECLARE @begintime DATETIME
SET @begintime= GETDATE()
--查询代理人所在村的订单
--参数部分
DECLARE
@PageIndex INT, --指定页
@PageSize INT, --每页记录数
@TotalNumber INT, --返回记录总数
@AgentID VARCHAR(36), --代理人ID
@GoodsName VARCHAR(50),
@OrderSeqNo VARCHAR(36),
@FarmerName VARCHAR(50),
@OrderStatus VARCHAR(6),
@CompanyName VARCHAR(100),
@PlaceTimeStart DATETIME,
@PlaceTimeEnd DATETIME
--参数赋值
SET @PageIndex=11
SET @PageSize=10
SET @AgentID='0ff6f6e1-a111-4523-bbd0-4ce28e8dda56'
SET @GoodsName=''
SET @OrderSeqNo=''
SET @FarmerName=''
SET @OrderStatus=''
SET @CompanyName=''
-- @PlaceTimeStart DATETIME,
-- @PlaceTimeEnd DATETIME
--临时变量定义
DECLARE @VillageID VARCHAR(36) --村ID
SELECT @VillageID=ai.VillageID FROM AgentInfo ai WHERE ai.AgentID=@AgentID
--临时表#PurchaseDoc,仅用于存储查询出来的PurchaseDocID和农民Name
DECLARE @PurchaseDoc TABLE (
PurchaseDocID VARCHAR(36),
[NAME] VARCHAR(50)
);
--查询指定村的购买单号和农民名字到#PurchaseDoc
INSERT INTO @PurchaseDoc(PurchaseDocID,[NAME])
SELECT pd.PurchaseDocID,pi1.[Name]
FROM PurchaseDoc pd INNER JOIN PlanterInfo pi1
ON pd.PlanterID=pi1.PlanterID
WHERE pi1.VillageID=@VillageID
--获取记录总数,未设置查询条件
SELECT @TotalNumber=COUNT(*)
FROM OrderInfo oi INNER JOIN @PurchaseDoc
ON oi.PurchaseDocID=[@PurchaseDoc].PurchaseDocID
AND [@PurchaseDoc].[NAME] LIKE '%'+@FarmerName+'%'
INNER JOIN CompanyInfo ci ON oi.CompanyID=ci.CompanyID
AND oi.GoodsName LIKE '%'+@GoodsName+'%'
AND oi.OrderSeqNO LIKE '%'+@OrderSeqNo+'%'
AND ci.CompanyName LIKE '%'+@CompanyName+'%'
And oi.OrderStatus LIKE '%'+@OrderStatus+'%'
AND (oi.PlaceTime BETWEEN ISNULL(@PlaceTimeStart,'1900-01-01') AND ISNULL(@PlaceTimeEnd,'9999-12-31'))
;
--定义并计算指定页首记录编号
DECLARE @StartNumber INT,@TotalPages INT
SET @TotalPages=CEILING(@TotalNumber/@PageSize)
SET @StartNumber=
CASE
WHEN @PageIndex<=1 THEN 1
WHEN @PageIndex>@TotalPages THEN (@TotalPages-1)*@PageSize+1
ELSE (@PageIndex-1)*@PageSize+1
END;
--临时订单表
DECLARE @OrderInfo TABLE (
OrderSeqNO VARCHAR(36),
GoodsName VARCHAR(50),
--CompanyID VARCHAR(36),
CompanyName VARCHAR(100),
GoodsType VARCHAR(3),
OrderAmount DECIMAL(12,2),
PlaceTime DATETIME,
DeliveryTime DATETIME,
OrderStatus VARCHAR(6),
PurchaseDocID VARCHAR(36),
GoodsID VARCHAR(36)
);
--查询当前页的订单信息到临时订单表
WITH cte AS (
SELECT ROW_NUMBER() OVER (ORDER BY oi.PlaceTime DESC) AS RowNumber,
oi.OrderSeqNO ,
oi.GoodsName ,
--oi.CompanyID,
ci.CompanyName ,
oi.GoodsType ,
oi.OrderAmount ,
oi.PlaceTime ,
oi.DeliveryTime ,
oi.OrderStatus,
oi.PurchaseDocID,
oi.GoodsID
FROM OrderInfo oi INNER JOIN @PurchaseDoc
ON oi.PurchaseDocID=[@PurchaseDoc].PurchaseDocID
AND [@PurchaseDoc].[NAME] LIKE '%'+@FarmerName+'%'
INNER JOIN CompanyInfo ci ON oi.CompanyID=ci.CompanyID
AND oi.GoodsName LIKE '%'+@GoodsName+'%'
AND oi.OrderSeqNO LIKE '%'+@OrderSeqNo+'%'
AND ci.CompanyName LIKE '%'+@CompanyName+'%'
And oi.OrderStatus LIKE '%'+@OrderStatus+'%'
AND (oi.PlaceTime BETWEEN ISNULL(@PlaceTimeStart,'1900-01-01') AND ISNULL(@PlaceTimeEnd,'9999-12-31'))
)
INSERT INTO @OrderInfo
SELECT cte.OrderSeqNO ,
cte.GoodsName ,
--cte.CompanyID ,
cte.CompanyName,
cte.GoodsType ,
cte.OrderAmount ,
cte.PlaceTime ,
cte.DeliveryTime ,
cte.OrderStatus,
cte.PurchaseDocID,
cte.GoodsID
FROM cte WHERE RowNumber BETWEEN @StartNumber AND (@StartNumber+@PageSize-1)
--获取用于显示的订单信息
SELECT oi.OrderSeqNO,
oi.GoodsName,
--ci.CompanyName,
oi.CompanyName,
oi.GoodsType,
oi.OrderAmount,
oi.PlaceTime,
oi.DeliveryTime,
oi.OrderStatus,
pd.[NAME]
FROM @OrderInfo oi
INNER JOIN @PurchaseDoc pd ON oi.PurchaseDocID=pd.PurchaseDocID
--INNER JOIN CompanyInfo ci ON oi.CompanyID=ci.CompanyID
--获取订单的详细信息
--SELECT vpps.PkgAmount,vpps.UnitCnName,vpps.PkgSpecName,od.Qty,od.UnitPrice,od.OrderSeqNO
-- FROM OrderDetail od
--INNER JOIN @OrderInfo oi ON od.OrderSeqNO=oi.OrderSeqNO
--INNER JOIN View_ProductPkgSpec vpps ON od.GoodsID=vpps.PkgSpecID
SELECT sps.PkgAmount,u.UnitCnName,ps.PkgSpecName,od.Qty,od.UnitPrice,od.OrderSeqNO
FROM @OrderInfo oi
INNER JOIN OrderDetail od ON oi.OrderSeqNO=od.OrderSeqNO AND oi.GoodsType='2'
INNER JOIN SeedPkgSpec sps ON od.GoodsID=sps.SeedPkgSpecID
INNER JOIN Unit u ON sps.Unit=u.UnitCode
INNER JOIN PkgSpec ps ON sps.PkgSpecName=ps.PkgSpecCode
UNION
SELECT pps.PkgAmount,u.UnitCnName,ps.PkgSpecName,od.Qty,od.UnitPrice,od.OrderSeqNO
FROM @OrderInfo oi
INNER JOIN OrderDetail od ON oi.OrderSeqNO=od.OrderSeqNO AND oi.GoodsType='3'
INNER JOIN PesPkgSpec pps ON od.GoodsID=pps.PesPkgSpecID
INNER JOIN Unit u ON pps.Unit=u.UnitCode
INNER JOIN PkgSpec ps ON pps.PkgSpecName=ps.PkgSpecCode
UNION
SELECT fps.PkgAmount,u.UnitCnName,ps.PkgSpecName,od.Qty,od.UnitPrice,od.OrderSeqNO
FROM @OrderInfo oi
INNER JOIN OrderDetail od ON oi.OrderSeqNO=od.OrderSeqNO AND oi.GoodsType='4'
INNER JOIN FertPkgSpec fps ON od.GoodsID=fps.FertPkgSpecID
INNER JOIN Unit u ON fps.Unit=u.UnitCode
INNER JOIN PkgSpec ps ON fps.PkgSpecName=ps.PkgSpecCode
SELECT DATEDIFF(ms,@begintime,GETDATE())
SET @begintime= GETDATE()
--查询代理人所在村的订单
--参数部分
DECLARE
@PageIndex INT, --指定页
@PageSize INT, --每页记录数
@TotalNumber INT, --返回记录总数
@AgentID VARCHAR(36), --代理人ID
@GoodsName VARCHAR(50),
@OrderSeqNo VARCHAR(36),
@FarmerName VARCHAR(50),
@OrderStatus VARCHAR(6),
@CompanyName VARCHAR(100),
@PlaceTimeStart DATETIME,
@PlaceTimeEnd DATETIME
--参数赋值
SET @PageIndex=11
SET @PageSize=10
SET @AgentID='0ff6f6e1-a111-4523-bbd0-4ce28e8dda56'
SET @GoodsName=''
SET @OrderSeqNo=''
SET @FarmerName=''
SET @OrderStatus=''
SET @CompanyName=''
-- @PlaceTimeStart DATETIME,
-- @PlaceTimeEnd DATETIME
--临时变量定义
DECLARE @VillageID VARCHAR(36) --村ID
SELECT @VillageID=ai.VillageID FROM AgentInfo ai WHERE ai.AgentID=@AgentID
--临时表#PurchaseDoc,仅用于存储查询出来的PurchaseDocID和农民Name
DECLARE @PurchaseDoc TABLE (
PurchaseDocID VARCHAR(36),
[NAME] VARCHAR(50)
);
--查询指定村的购买单号和农民名字到#PurchaseDoc
INSERT INTO @PurchaseDoc(PurchaseDocID,[NAME])
SELECT pd.PurchaseDocID,pi1.[Name]
FROM PurchaseDoc pd INNER JOIN PlanterInfo pi1
ON pd.PlanterID=pi1.PlanterID
WHERE pi1.VillageID=@VillageID
--获取记录总数,未设置查询条件
SELECT @TotalNumber=COUNT(*)
FROM OrderInfo oi INNER JOIN @PurchaseDoc
ON oi.PurchaseDocID=[@PurchaseDoc].PurchaseDocID
AND [@PurchaseDoc].[NAME] LIKE '%'+@FarmerName+'%'
INNER JOIN CompanyInfo ci ON oi.CompanyID=ci.CompanyID
AND oi.GoodsName LIKE '%'+@GoodsName+'%'
AND oi.OrderSeqNO LIKE '%'+@OrderSeqNo+'%'
AND ci.CompanyName LIKE '%'+@CompanyName+'%'
And oi.OrderStatus LIKE '%'+@OrderStatus+'%'
AND (oi.PlaceTime BETWEEN ISNULL(@PlaceTimeStart,'1900-01-01') AND ISNULL(@PlaceTimeEnd,'9999-12-31'))
;
--定义并计算指定页首记录编号
DECLARE @StartNumber INT,@TotalPages INT
SET @TotalPages=CEILING(@TotalNumber/@PageSize)
SET @StartNumber=
CASE
WHEN @PageIndex<=1 THEN 1
WHEN @PageIndex>@TotalPages THEN (@TotalPages-1)*@PageSize+1
ELSE (@PageIndex-1)*@PageSize+1
END;
--临时订单表
DECLARE @OrderInfo TABLE (
OrderSeqNO VARCHAR(36),
GoodsName VARCHAR(50),
--CompanyID VARCHAR(36),
CompanyName VARCHAR(100),
GoodsType VARCHAR(3),
OrderAmount DECIMAL(12,2),
PlaceTime DATETIME,
DeliveryTime DATETIME,
OrderStatus VARCHAR(6),
PurchaseDocID VARCHAR(36),
GoodsID VARCHAR(36)
);
--查询当前页的订单信息到临时订单表
WITH cte AS (
SELECT ROW_NUMBER() OVER (ORDER BY oi.PlaceTime DESC) AS RowNumber,
oi.OrderSeqNO ,
oi.GoodsName ,
--oi.CompanyID,
ci.CompanyName ,
oi.GoodsType ,
oi.OrderAmount ,
oi.PlaceTime ,
oi.DeliveryTime ,
oi.OrderStatus,
oi.PurchaseDocID,
oi.GoodsID
FROM OrderInfo oi INNER JOIN @PurchaseDoc
ON oi.PurchaseDocID=[@PurchaseDoc].PurchaseDocID
AND [@PurchaseDoc].[NAME] LIKE '%'+@FarmerName+'%'
INNER JOIN CompanyInfo ci ON oi.CompanyID=ci.CompanyID
AND oi.GoodsName LIKE '%'+@GoodsName+'%'
AND oi.OrderSeqNO LIKE '%'+@OrderSeqNo+'%'
AND ci.CompanyName LIKE '%'+@CompanyName+'%'
And oi.OrderStatus LIKE '%'+@OrderStatus+'%'
AND (oi.PlaceTime BETWEEN ISNULL(@PlaceTimeStart,'1900-01-01') AND ISNULL(@PlaceTimeEnd,'9999-12-31'))
)
INSERT INTO @OrderInfo
SELECT cte.OrderSeqNO ,
cte.GoodsName ,
--cte.CompanyID ,
cte.CompanyName,
cte.GoodsType ,
cte.OrderAmount ,
cte.PlaceTime ,
cte.DeliveryTime ,
cte.OrderStatus,
cte.PurchaseDocID,
cte.GoodsID
FROM cte WHERE RowNumber BETWEEN @StartNumber AND (@StartNumber+@PageSize-1)
--获取用于显示的订单信息
SELECT oi.OrderSeqNO,
oi.GoodsName,
--ci.CompanyName,
oi.CompanyName,
oi.GoodsType,
oi.OrderAmount,
oi.PlaceTime,
oi.DeliveryTime,
oi.OrderStatus,
pd.[NAME]
FROM @OrderInfo oi
INNER JOIN @PurchaseDoc pd ON oi.PurchaseDocID=pd.PurchaseDocID
--INNER JOIN CompanyInfo ci ON oi.CompanyID=ci.CompanyID
--获取订单的详细信息
--SELECT vpps.PkgAmount,vpps.UnitCnName,vpps.PkgSpecName,od.Qty,od.UnitPrice,od.OrderSeqNO
-- FROM OrderDetail od
--INNER JOIN @OrderInfo oi ON od.OrderSeqNO=oi.OrderSeqNO
--INNER JOIN View_ProductPkgSpec vpps ON od.GoodsID=vpps.PkgSpecID
SELECT sps.PkgAmount,u.UnitCnName,ps.PkgSpecName,od.Qty,od.UnitPrice,od.OrderSeqNO
FROM @OrderInfo oi
INNER JOIN OrderDetail od ON oi.OrderSeqNO=od.OrderSeqNO AND oi.GoodsType='2'
INNER JOIN SeedPkgSpec sps ON od.GoodsID=sps.SeedPkgSpecID
INNER JOIN Unit u ON sps.Unit=u.UnitCode
INNER JOIN PkgSpec ps ON sps.PkgSpecName=ps.PkgSpecCode
UNION
SELECT pps.PkgAmount,u.UnitCnName,ps.PkgSpecName,od.Qty,od.UnitPrice,od.OrderSeqNO
FROM @OrderInfo oi
INNER JOIN OrderDetail od ON oi.OrderSeqNO=od.OrderSeqNO AND oi.GoodsType='3'
INNER JOIN PesPkgSpec pps ON od.GoodsID=pps.PesPkgSpecID
INNER JOIN Unit u ON pps.Unit=u.UnitCode
INNER JOIN PkgSpec ps ON pps.PkgSpecName=ps.PkgSpecCode
UNION
SELECT fps.PkgAmount,u.UnitCnName,ps.PkgSpecName,od.Qty,od.UnitPrice,od.OrderSeqNO
FROM @OrderInfo oi
INNER JOIN OrderDetail od ON oi.OrderSeqNO=od.OrderSeqNO AND oi.GoodsType='4'
INNER JOIN FertPkgSpec fps ON od.GoodsID=fps.FertPkgSpecID
INNER JOIN Unit u ON fps.Unit=u.UnitCode
INNER JOIN PkgSpec ps ON fps.PkgSpecName=ps.PkgSpecCode
SELECT DATEDIFF(ms,@begintime,GETDATE())
![](https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif)
DECLARE @begintime DATETIME
SET @begintime= GETDATE()
--查询厂商订单,OrderStatus条件未设置
--参数部分
DECLARE
@PageIndex INT, --指定页
@PageSize INT, --每页记录数
@TotalNumber INT, --返回记录总数
@CompanyID VARCHAR(36), --厂商ID
@DisCode VARCHAR(12),
@GoodsName VARCHAR(50),
@GoodsType VARCHAR(3),
@OrderAmountStart DECIMAL(12,2),
@OrderAmountEnd DECIMAL(12,2),
@PlaceTimeStart DATETIME,
@PlaceTimeEnd DATETIME
--@OrderStatus VARCHAR(6)
--参数赋值
SET @PageIndex=110
SET @PageSize=10
SET @CompanyID='22177BE9-AA3F-4F3C-ABDC-5AB2ECD50658'
SET @DisCode=''
SET @GoodsName=''
SET @GoodsType=''
SET @OrderAmountStart=0
SET @OrderAmountEnd=9999999999.99
--去掉地域代码参数右侧为0的部分
SET @DisCode=
CASE
WHEN @DisCode LIKE '__0000000000' THEN LEFT(@DisCode,2)
WHEN @DisCode LIKE '____00000000' THEN LEFT(@DisCode,4)
WHEN @DisCode LIKE '______000000' THEN LEFT(@DisCode,6)
WHEN @DisCode LIKE '_________000' THEN LEFT(@DisCode,9)
ELSE @DisCode
END;
--获取记录总数,未设置查询条件
SELECT @TotalNumber=COUNT(*) FROM OrderInfo oi
INNER JOIN PurchaseDoc pd ON oi.PurchaseDocID=pd.PurchaseDocID
AND oi.CompanyID=@CompanyID
AND pd.DisCode LIKE @DisCode+'%'
AND oi.GoodsName LIKE '%'+@GoodsName+'%'
AND oi.GoodsType LIKE '%'+@GoodsType+'%'
AND oi.OrderAmount BETWEEN @OrderAmountStart AND @OrderAmountEnd
AND oi.PlaceTime BETWEEN ISNULL(@PlaceTimeStart,'1900-01-01') AND ISNULL(@PlaceTimeEnd,'9999-12-31')
--And (其他条件)oi.OrderStatus=
--定义并计算指定页首记录编号
DECLARE @StartNumber INT,@TotalPages INT
SET @TotalPages=CEILING(@TotalNumber/@PageSize)
SET @StartNumber=
CASE
WHEN @PageIndex<=1 THEN 1
WHEN @PageIndex>@TotalPages THEN (@TotalPages-1)*@PageSize+1
ELSE (@PageIndex-1)*@PageSize+1
END;
--临时订单表
DECLARE @OrderInfo TABLE (
OrderSeqNO VARCHAR(36),
GoodsName VARCHAR(50),
GoodsType VARCHAR(3),
OrderAmount DECIMAL(12,2),
PlaceTime DATETIME,
Consignee VARCHAR(50),
ConsigneeTel VARCHAR(20),
PurchaseDocID VARCHAR(36),
ArrivalTime DATETIME,
ConsigneeAddress VARCHAR(200),
PostCode VARCHAR(6)
);
--查询当前页的订单信息到临时订单表
WITH cte AS (
SELECT ROW_NUMBER() OVER (ORDER BY oi.PlaceTime ASC) AS RowNumber,
oi.OrderSeqNO ,
oi.GoodsName ,
oi.GoodsType ,
oi.OrderAmount ,
oi.PlaceTime ,
pd.Consignee ,
pd.ConsigneeTel ,
pd.PurchaseDocID,
oi.ArrivalTime,
pd.ConsigneeAddress,
pd.PostCode
FROM OrderInfo oi
INNER JOIN PurchaseDoc pd ON oi.PurchaseDocID=pd.PurchaseDocID
AND oi.CompanyID=@CompanyID
AND pd.DisCode LIKE @DisCode+'%'
AND oi.GoodsName LIKE '%'+@GoodsName+'%'
AND oi.GoodsType LIKE '%'+@GoodsType+'%'
AND oi.OrderAmount BETWEEN @OrderAmountStart AND @OrderAmountEnd
AND oi.PlaceTime BETWEEN ISNULL(@PlaceTimeStart,'1900-01-01') AND ISNULL(@PlaceTimeEnd,'9999-12-31')
--And (其他条件)oi.OrderStatus=
)
INSERT INTO @OrderInfo(
OrderSeqNO,
GoodsName ,
GoodsType ,
OrderAmount,
PlaceTime,
Consignee ,
ConsigneeTel ,
PurchaseDocID,
ArrivalTime,
ConsigneeAddress,
PostCode
)
SELECT cte.OrderSeqNO,
cte.GoodsName ,
cte.GoodsType ,
cte.OrderAmount,
cte.PlaceTime,
cte.Consignee ,
cte.ConsigneeTel ,
cte.PurchaseDocID,
cte.ArrivalTime,
cte.ConsigneeAddress,
cte.PostCode
FROM cte
WHERE cte.RowNumber BETWEEN @StartNumber AND (@StartNumber+@PageSize-1)
--获取订单信息
SELECT oi.* FROM @OrderInfo oi
--获取订单的详细信息
--SELECT vpps.PkgAmount,vpps.UnitCnName,vpps.PkgSpecName,od.Qty,od.UnitPrice,od.OrderSeqNO
-- FROM OrderDetail od
--INNER JOIN @OrderInfo oi ON od.OrderSeqNO=oi.OrderSeqNO
--INNER JOIN View_ProductPkgSpec vpps ON od.GoodsID=vpps.PkgSpecID
SELECT sps.PkgAmount,u.UnitCnName,ps.PkgSpecName,od.Qty,od.UnitPrice,od.OrderSeqNO
FROM @OrderInfo oi
INNER JOIN OrderDetail od ON oi.OrderSeqNO=od.OrderSeqNO AND oi.GoodsType='2'
INNER JOIN SeedPkgSpec sps ON od.GoodsID=sps.SeedPkgSpecID
INNER JOIN Unit u ON sps.Unit=u.UnitCode
INNER JOIN PkgSpec ps ON sps.PkgSpecName=ps.PkgSpecCode
UNION
SELECT pps.PkgAmount,u.UnitCnName,ps.PkgSpecName,od.Qty,od.UnitPrice,od.OrderSeqNO
FROM @OrderInfo oi
INNER JOIN OrderDetail od ON oi.OrderSeqNO=od.OrderSeqNO AND oi.GoodsType='3'
INNER JOIN PesPkgSpec pps ON od.GoodsID=pps.PesPkgSpecID
INNER JOIN Unit u ON pps.Unit=u.UnitCode
INNER JOIN PkgSpec ps ON pps.PkgSpecName=ps.PkgSpecCode
UNION
SELECT fps.PkgAmount,u.UnitCnName,ps.PkgSpecName,od.Qty,od.UnitPrice,od.OrderSeqNO
FROM @OrderInfo oi
INNER JOIN OrderDetail od ON oi.OrderSeqNO=od.OrderSeqNO AND oi.GoodsType='4'
INNER JOIN FertPkgSpec fps ON od.GoodsID=fps.FertPkgSpecID
INNER JOIN Unit u ON fps.Unit=u.UnitCode
INNER JOIN PkgSpec ps ON fps.PkgSpecName=ps.PkgSpecCode
SELECT DATEDIFF(ms,@begintime,GETDATE())
SET @begintime= GETDATE()
--查询厂商订单,OrderStatus条件未设置
--参数部分
DECLARE
@PageIndex INT, --指定页
@PageSize INT, --每页记录数
@TotalNumber INT, --返回记录总数
@CompanyID VARCHAR(36), --厂商ID
@DisCode VARCHAR(12),
@GoodsName VARCHAR(50),
@GoodsType VARCHAR(3),
@OrderAmountStart DECIMAL(12,2),
@OrderAmountEnd DECIMAL(12,2),
@PlaceTimeStart DATETIME,
@PlaceTimeEnd DATETIME
--@OrderStatus VARCHAR(6)
--参数赋值
SET @PageIndex=110
SET @PageSize=10
SET @CompanyID='22177BE9-AA3F-4F3C-ABDC-5AB2ECD50658'
SET @DisCode=''
SET @GoodsName=''
SET @GoodsType=''
SET @OrderAmountStart=0
SET @OrderAmountEnd=9999999999.99
--去掉地域代码参数右侧为0的部分
SET @DisCode=
CASE
WHEN @DisCode LIKE '__0000000000' THEN LEFT(@DisCode,2)
WHEN @DisCode LIKE '____00000000' THEN LEFT(@DisCode,4)
WHEN @DisCode LIKE '______000000' THEN LEFT(@DisCode,6)
WHEN @DisCode LIKE '_________000' THEN LEFT(@DisCode,9)
ELSE @DisCode
END;
--获取记录总数,未设置查询条件
SELECT @TotalNumber=COUNT(*) FROM OrderInfo oi
INNER JOIN PurchaseDoc pd ON oi.PurchaseDocID=pd.PurchaseDocID
AND oi.CompanyID=@CompanyID
AND pd.DisCode LIKE @DisCode+'%'
AND oi.GoodsName LIKE '%'+@GoodsName+'%'
AND oi.GoodsType LIKE '%'+@GoodsType+'%'
AND oi.OrderAmount BETWEEN @OrderAmountStart AND @OrderAmountEnd
AND oi.PlaceTime BETWEEN ISNULL(@PlaceTimeStart,'1900-01-01') AND ISNULL(@PlaceTimeEnd,'9999-12-31')
--And (其他条件)oi.OrderStatus=
--定义并计算指定页首记录编号
DECLARE @StartNumber INT,@TotalPages INT
SET @TotalPages=CEILING(@TotalNumber/@PageSize)
SET @StartNumber=
CASE
WHEN @PageIndex<=1 THEN 1
WHEN @PageIndex>@TotalPages THEN (@TotalPages-1)*@PageSize+1
ELSE (@PageIndex-1)*@PageSize+1
END;
--临时订单表
DECLARE @OrderInfo TABLE (
OrderSeqNO VARCHAR(36),
GoodsName VARCHAR(50),
GoodsType VARCHAR(3),
OrderAmount DECIMAL(12,2),
PlaceTime DATETIME,
Consignee VARCHAR(50),
ConsigneeTel VARCHAR(20),
PurchaseDocID VARCHAR(36),
ArrivalTime DATETIME,
ConsigneeAddress VARCHAR(200),
PostCode VARCHAR(6)
);
--查询当前页的订单信息到临时订单表
WITH cte AS (
SELECT ROW_NUMBER() OVER (ORDER BY oi.PlaceTime ASC) AS RowNumber,
oi.OrderSeqNO ,
oi.GoodsName ,
oi.GoodsType ,
oi.OrderAmount ,
oi.PlaceTime ,
pd.Consignee ,
pd.ConsigneeTel ,
pd.PurchaseDocID,
oi.ArrivalTime,
pd.ConsigneeAddress,
pd.PostCode
FROM OrderInfo oi
INNER JOIN PurchaseDoc pd ON oi.PurchaseDocID=pd.PurchaseDocID
AND oi.CompanyID=@CompanyID
AND pd.DisCode LIKE @DisCode+'%'
AND oi.GoodsName LIKE '%'+@GoodsName+'%'
AND oi.GoodsType LIKE '%'+@GoodsType+'%'
AND oi.OrderAmount BETWEEN @OrderAmountStart AND @OrderAmountEnd
AND oi.PlaceTime BETWEEN ISNULL(@PlaceTimeStart,'1900-01-01') AND ISNULL(@PlaceTimeEnd,'9999-12-31')
--And (其他条件)oi.OrderStatus=
)
INSERT INTO @OrderInfo(
OrderSeqNO,
GoodsName ,
GoodsType ,
OrderAmount,
PlaceTime,
Consignee ,
ConsigneeTel ,
PurchaseDocID,
ArrivalTime,
ConsigneeAddress,
PostCode
)
SELECT cte.OrderSeqNO,
cte.GoodsName ,
cte.GoodsType ,
cte.OrderAmount,
cte.PlaceTime,
cte.Consignee ,
cte.ConsigneeTel ,
cte.PurchaseDocID,
cte.ArrivalTime,
cte.ConsigneeAddress,
cte.PostCode
FROM cte
WHERE cte.RowNumber BETWEEN @StartNumber AND (@StartNumber+@PageSize-1)
--获取订单信息
SELECT oi.* FROM @OrderInfo oi
--获取订单的详细信息
--SELECT vpps.PkgAmount,vpps.UnitCnName,vpps.PkgSpecName,od.Qty,od.UnitPrice,od.OrderSeqNO
-- FROM OrderDetail od
--INNER JOIN @OrderInfo oi ON od.OrderSeqNO=oi.OrderSeqNO
--INNER JOIN View_ProductPkgSpec vpps ON od.GoodsID=vpps.PkgSpecID
SELECT sps.PkgAmount,u.UnitCnName,ps.PkgSpecName,od.Qty,od.UnitPrice,od.OrderSeqNO
FROM @OrderInfo oi
INNER JOIN OrderDetail od ON oi.OrderSeqNO=od.OrderSeqNO AND oi.GoodsType='2'
INNER JOIN SeedPkgSpec sps ON od.GoodsID=sps.SeedPkgSpecID
INNER JOIN Unit u ON sps.Unit=u.UnitCode
INNER JOIN PkgSpec ps ON sps.PkgSpecName=ps.PkgSpecCode
UNION
SELECT pps.PkgAmount,u.UnitCnName,ps.PkgSpecName,od.Qty,od.UnitPrice,od.OrderSeqNO
FROM @OrderInfo oi
INNER JOIN OrderDetail od ON oi.OrderSeqNO=od.OrderSeqNO AND oi.GoodsType='3'
INNER JOIN PesPkgSpec pps ON od.GoodsID=pps.PesPkgSpecID
INNER JOIN Unit u ON pps.Unit=u.UnitCode
INNER JOIN PkgSpec ps ON pps.PkgSpecName=ps.PkgSpecCode
UNION
SELECT fps.PkgAmount,u.UnitCnName,ps.PkgSpecName,od.Qty,od.UnitPrice,od.OrderSeqNO
FROM @OrderInfo oi
INNER JOIN OrderDetail od ON oi.OrderSeqNO=od.OrderSeqNO AND oi.GoodsType='4'
INNER JOIN FertPkgSpec fps ON od.GoodsID=fps.FertPkgSpecID
INNER JOIN Unit u ON fps.Unit=u.UnitCode
INNER JOIN PkgSpec ps ON fps.PkgSpecName=ps.PkgSpecCode
SELECT DATEDIFF(ms,@begintime,GETDATE())
欢迎大家一起讨论!!!!