SQL中使用WITH AS提高性能（二）

最新推荐文章于 2021-02-07 12:42:17 发布

weixin_34254823

最新推荐文章于 2021-02-07 12:42:17 发布

阅读量184

点赞数

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/sheseido/p/3471419.html

继上一节

对比两条查询，第一是用了with as 第二条语句没用with as 查看执行计划的效果

WITH    vep
          AS ( SELECT   package.OrderCode ,
                        RANK() OVER ( PARTITION BY package.OrderCode ORDER BY package.ID ) AS Index ,
                        COUNT(1) OVER ( PARTITION BY package.OrderCode ) AS Count
               FROM     dbo.BookPackages package WITH ( NOLOCK )
             )
    SELECT TOP 100
            *
    FROM    vep
    WHERE   vep.Count > 2

SELECT TOP 100 * FROM (
SELECT 
	vep.OrderCode,
	ROW_NUMBER() OVER(PARTITION BY vep.OrderCode ORDER BY vep.ID ) 'Index',
	COUNT(1) OVER(PARTITION BY vep.OrderCode) 'Count'
FROM 
	dbo.BookPackages