分页存储过程（五）在MS SQL Server中打造更加准确，且有一点效率提升的的分页结果

　　以前的分页我都是主子表join查询，然后会出现下面的记录形式，返回时一个数据集合

 

　　

 

    订单1　　明细1.1

　　订单1　　明细1.2

　　订单1　　明细1.3

　　订单2　　明细2.1

　　订单2　　明细2.2

 

　　这样的话，就造成大量的数据冗余，就是【订单1】的数据被重复多次从数据库读取。这次我尝试将他们分开返回，返回两个数据集合

　　

　　订单集合

　　

 

 

    订单1

　　订单2

　　订单3

 

　　明细集合

　　

    明细1.1 

　　明细1.2

　　明细1.3

　　明细2.1

　　明细2.2

 

　　

　　这样的结果不知道会不会有效率提升，还有待测试。但是数据量少了，联合的数据量也少了。以前需要联合整张订单表和整张明细表，现在首先将符合条件的订单找到，然后用符合条件的订单和明细表联合，直接查找符合条件的订单的明细，目标明确了。

　　当然了，找到的符合条件的订单还是放在零时表中，然后用临时表和明细表进行inner join查询明细信息。

　　

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())

 

 

 

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())

 

 

　　欢迎大家一起讨论！！！！