设为首页 收藏本站
查看: 1253|回复: 0

[经验分享] 分页存储过程(五)在MS SQL Server中打造更加准确,且有一点效率提升的的分页结果

[复制链接]

尚未签到

发表于 2016-11-10 07:52:53 | 显示全部楼层 |阅读模式
  以前的分页我都是主子表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查询明细信息。
  
DSC0000.gif DSC0001.gif 代码<!--<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 DESCAS 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 ASCAS 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<span

运维网声明 1、欢迎大家加入本站运维交流群:群②:261659950 群⑤:202807635 群⑦870801961 群⑧679858003
2、本站所有主题由该帖子作者发表,该帖子作者与运维网享有帖子相关版权
3、所有作品的著作权均归原作者享有,请您和我们一样尊重他人的著作权等合法权益。如果您对作品感到满意,请购买正版
4、禁止制作、复制、发布和传播具有反动、淫秽、色情、暴力、凶杀等内容的信息,一经发现立即删除。若您因此触犯法律,一切后果自负,我们对此不承担任何责任
5、所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其内容的准确性、可靠性、正当性、安全性、合法性等负责,亦不承担任何法律责任
6、所有作品仅供您个人学习、研究或欣赏,不得用于商业或者其他用途,否则,一切后果均由您自己承担,我们对此不承担任何法律责任
7、如涉及侵犯版权等问题,请您及时通知我们,我们将立即采取措施予以解决
8、联系人Email:admin@iyunv.com 网址:www.yunweiku.com

所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其承担任何法律责任,如涉及侵犯版权等问题,请您及时通知我们,我们将立即处理,联系人Email:kefu@iyunv.com,QQ:1061981298 本贴地址:https://www.yunweiku.com/thread-298153-1-1.html 上篇帖子: SQL Server 2005查看触发器信息 下篇帖子: 还原数据库备份文件的时候报独占错误 Sql Server 2005
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

扫码加入运维网微信交流群X

扫码加入运维网微信交流群

扫描二维码加入运维网微信交流群,最新一手资源尽在官方微信交流群!快快加入我们吧...

扫描微信二维码查看详情

客服E-mail:kefu@iyunv.com 客服QQ:1061981298


QQ群⑦:运维网交流群⑦ QQ群⑧:运维网交流群⑧ k8s群:运维网kubernetes交流群


提醒:禁止发布任何违反国家法律、法规的言论与图片等内容;本站内容均来自个人观点与网络等信息,非本站认同之观点.


本站大部分资源是网友从网上搜集分享而来,其版权均归原作者及其网站所有,我们尊重他人的合法权益,如有内容侵犯您的合法权益,请及时与我们联系进行核实删除!



合作伙伴: 青云cloud

快速回复 返回顶部 返回列表