继上一节
对比两条查询,第一是用了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
vep WITH(NOLOCK) ) a WHERE a.Count>2
明显用了with as 快了很多
中间用到了 rank() over (partition by over by) 语句,下一篇再写