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

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

 

　　

 

<!--<br/ /><br/ />Code highlighting produced by Actipro CodeHighlighter (freeware)<br/ />http://www.CodeHighlighter.com/<br/ /><br/ />-->     订单1　　明细1.1

　　订单1　　明细1.2

　　订单1　　明细1.3

　　订单2　　明细2.1

　　订单2　　明细2.2

 

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

　　

　　订单集合

　　

 

 

<!--<br/ /><br/ />Code highlighting produced by Actipro CodeHighlighter (freeware)<br/ />http://www.CodeHighlighter.com/<br/ /><br/ />-->     订单1

　　订单2

　　订单3

 

　　明细集合

　　

<!--<br/ /><br/ />Code highlighting produced by Actipro CodeHighlighter (freeware)<br/ />http://www.CodeHighlighter.com/<br/ /><br/ />-->     明细1.1 

　　明细1.2

　　明细1.3

　　明细2.1

　　明细2.2

 

　　

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

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

　　

代码

<!--<br/ /><br/ />Code highlighting produced by Actipro CodeHighlighter (freeware)<br/ />http://www.CodeHighlighter.com/<br/ /><br/ />--> 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 ())

 

 

 

代码

<!--<br/ /><br/ />Code highlighting produced by Actipro CodeHighlighter (freeware)<br/ />http://www.CodeHighlighter.com/<br/ /><br/ />--> 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