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

继上一节

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

?

1 2 3 4 5 6 7 8 9 10

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

　　

?

1 2 3 4 5 6 7

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 vep WITH(NOLOCK) ) a WHERE a.Count>2

　　明显用了with as 快了很多

中间用到了 rank() over (partition by over by) 语句，下一篇再写