一. WITH AS 的含义
WITH AS 短语,也叫做子查询部分(subquery factoring),可以让你做很多事情,定义一个SQL片断,该SQL片断会
被整个SQL语句所用到。有的时候,是为了让SQL语句的可读性更高些,也有可能是在 UNION ALL 的不同部分,作为提供数
据的部分。
特别对于 UNION ALL 比较有用。因为 UNION ALL 的每个部分可能相同,但是如果每个部分都去执行一遍的话,则成本太高,
所以可以使用 WITH AS 短语,则只要执行一遍即可。如果 WITH AS 短语所定义的表名被调用两次以上,则优化器会自动将
WITH AS 短语所获取的数据放入一个 TEMP 表里,如果只是被调用一次,则不会。而提示materialize则是强制将 WITH AS
短语里的数据放入一个全局临时表里。很多查询通过这种方法都可以提高速度。

with as语法
针对一个别名
with tmp as (select * from tb_name)

针对多个别名
with
tmp as (select * from tb_name),
tmp2 as (select * from tb_name2),
tmp3 as (select * from tb_name3),

–相当于建了个temp临时表
with temp as (select * from proj_appl)
select * from temp;

–相当于建了temp1、temp2临时表
with
temp1 as (select * from proj_appl),
temp2 as (select * from project)
select * from temp1, temp2 where temp1.proj_Id = temp2.proj_Id;

其实就是把一大堆重复用到的sql语句放在with as里面,取一个别名,后面的查询就可以用它,
这样对于大批量的sql语句起到一个优化的作用,而且清楚明了。

WITH语句的优点:
(1) SQL可读性增强。比如对于特定with子查询取个有意义的名字等。

(2) with子查询只执行一次,将结果存储在用户临时表空间中,可以引用多次,增强性能。

使用场景:
当通过一次查询,查询不出来用户需要的数据的时候,比如需要先查询出来一组数据,然后再根据里面的某些数据再去查询需要的数据的时候,就可以使用with as 这种结构的语句,这样可以避免创建临时表,可以通过一次sql调用直接查询出来,而不用在应用程序里面调用两次去查询。

with
temp as (select * from proj_appl)
select * from project p,temp t where temp.proj_Id = t.proj_Id;

–使用递归公用表表达式显示递归的多个级别
WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS
(
SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
FROM HumanResources.Employee
WHERE ManagerID IS NULL
UNION ALL
SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
FROM HumanResources.Employee e
INNER JOIN DirectReports d
ON e.ManagerID = d.EmployeeID
)
SELECT ManagerID, EmployeeID, EmployeeLevel
FROM DirectReports ;

–使用递归公用表表达式显示递归的两个级别
WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS
(
SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
FROM HumanResources.Employee
WHERE ManagerID IS NULL
UNION ALL
SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
FROM HumanResources.Employee e
INNER JOIN DirectReports d
ON e.ManagerID = d.EmployeeID
)
SELECT ManagerID, EmployeeID, EmployeeLevel
FROM DirectReports
WHERE EmployeeLevel <= 2

–使用递归公用表表达式显示层次列表
WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort)
AS (SELECT CONVERT(varchar(255), c.FirstName + ’ ’ + c.LastName),
e.Title,
e.EmployeeID,
1,
CONVERT(varchar(255), c.FirstName + ’ ’ + c.LastName)
FROM HumanResources.Employee AS e
JOIN Person.Contact AS c ON e.ContactID = c.ContactID
WHERE e.ManagerID IS NULL
UNION ALL
SELECT CONVERT(varchar(255), REPLICATE ('| ’ , EmployeeLevel) +
c.FirstName + ’ ’ + c.LastName),
e.Title,
e.EmployeeID,
EmployeeLevel + 1,
CONVERT (varchar(255), RTRIM(Sort) + '| ’ + FirstName + ’ ’ +
LastName)
FROM HumanResources.Employee as e
JOIN Person.Contact AS c ON e.ContactID = c.ContactID
JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
)
SELECT EmployeeID, Name, Title, EmployeeLevel
FROM DirectReports
ORDER BY Sort

–使用 MAXRECURSION 取消一条语句
–可以使用 MAXRECURSION 来防止不合理的递归 CTE 进入无限循环。以下示例特意创建了一个无限循环,然后使用 MAXRECURSION 提示将递归级别限制为两级
WITH cte (EmployeeID, ManagerID, Title) as
(
SELECT EmployeeID, ManagerID, Title
FROM HumanResources.Employee
WHERE ManagerID IS NOT NULL
UNION ALL
SELECT cte.EmployeeID, cte.ManagerID, cte.Title
FROM cte
JOIN HumanResources.Employee AS e
ON cte.ManagerID = e.EmployeeID
)
–Uses MAXRECURSION to limit the recursive levels to 2
SELECT EmployeeID, ManagerID, Title
FROM cte
OPTION (MAXRECURSION 2)
–在更正代码错误之后,就不再需要 MAXRECURSION。以下示例显示了更正后的代码
WITH cte (EmployeeID, ManagerID, Title)
AS
(
SELECT EmployeeID, ManagerID, Title
FROM HumanResources.Employee
WHERE ManagerID IS NOT NULL
UNION ALL
SELECT e.EmployeeID, e.ManagerID, e.Title
FROM HumanResources.Employee AS e
JOIN cte ON e.ManagerID = cte.EmployeeID
)
SELECT EmployeeID, ManagerID, Title
FROM cte

Logo

为开发者提供学习成长、分享交流、生态实践、资源工具等服务,帮助开发者快速成长。

更多推荐