首页 > 代码库 > with as

with as

with as 短语,也叫做子查询部分(subquery factoring)公用表达式(CTE)。

使用范围:

sql2005及以上版本有效。

作用:

1、提高可读性 

-- 表名v_OrdersWITH tableBase AS (SELECT ROW_NUMBER() OVER (ORDER BY Id) AS rowNumber,*  FROM dbo.v_Orders WHERE 1=1)  SELECT * FROM tableBase WHERE rowNumber BETWEEN 1 AND 10

 2、提高性能

with as 所定义的表名被调用两次以上,则优化器会获取该表名下的数据放进一个temp表里。

注意

1、定义完CTE后,必须紧跟调用CTE的(select、delete、update、insert)SQL语句,否则定义失效。

2、定义多个CTE ,只需定义一个 with 且用 ‘,’ 连接

-- 表名v_OrdersWITH tableBase AS (SELECT ROW_NUMBER() OVER (ORDER BY Id) AS rowNumber,*  FROM dbo.v_Orders WHERE 1=1)  ,tableBase2 AS (SELECT ROW_NUMBER() OVER (ORDER BY Id) AS rowNumber,*  FROM dbo.v_Orders WHERE 2=2)  SELECT * FROM tableBase WHERE rowNumber BETWEEN 1 AND 10

3、CTE与表名、视图名重复时,紧跟在CTE后的第一个SQL语句使用的是CTE,后面的是表名或视图名

 

with as