SQLServer/MSSQL中使用with as实现递归查询(Using with as to realize recursive query in sqlserver / MSSQL)

什么是with

1、WITH AS短语,也叫做子查询部分(subquery factoring),可以定义一个SQL片断,该SQL片断会被整个SQL语句用到。可以使SQL语句的可读性更高,也可以在UNION ALL的不同部分,作为提供数据的部分。
2、对于UNION ALL,使用WITH AS定义了一个UNION ALL语句,当该片断被调用2次以上,优化器会自动将该WITH AS短语所获取的数据放入一个Temp表中。而提示meterialize则是强制将WITH AS短语的数据放入一个全局临时表中。很多查询通过该方式都可以提高速度。

语法

with as 在sql server 2005以后的版本可以使用(MySQL8.0及以后的版本可以使用该函数),称之为公用表表达式(CTE)。使用with as 可以提高SQL语句的可维护性,特别是涉及多个嵌套查询时。同时,同时,CTE要比表变量的效率高。

示例

WITH cte(f1, f2) AS
(SELECT 1, 2
UNION ALL
SELECT 3,4)

SELECT * FROM cte;
————————

什么是with

1. The phrase with as, also known as subquery factoring, can define an SQL fragment that will be used by the entire SQL statement. It can make the readability of SQL statements higher, or it can be used as a part of providing data in different parts of union all.
2. For union all, use with as to define a union all statement. When the fragment is called more than twice, the optimizer will automatically put the data obtained by the phrase with as into a temp table. The prompt materialize is to force the data of the with as phrase into a global temporary table. Many queries can speed up in this way.

grammar

With as can be used in versions after SQL Server 2005 (MySQL 8.0 and later versions can use this function), which is called common table expression (CTE). Using with as can improve the maintainability of SQL statements, especially when multiple nested queries are involved. At the same time, CTE is more efficient than table variables.

Examples

WITH cte(f1, f2) AS
(SELECT 1, 2
UNION ALL
SELECT 3,4)

SELECT * FROM cte;