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

virusswb

浏览: 495605 次
性别:
来自: 北京

最近访客更多访客>>

u012363178

yuanjin

epichust

minidanny

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

博客分类：

[网站分类]SQL Server

SQL Server SQL

　　以前的分页我都是主子表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())