The WITH Clause

最新推荐文章于 2021-10-17 18:41:48 发布

转载最新推荐文章于 2021-10-17 18:41:48 发布 · 534 阅读

文章标签：

#subquery #structure #query

SQL 专栏收录该内容

48 篇文章

订阅专栏

本文介绍了一种使用WITH子句来优化SQL查询的方法。通过将子查询定义与主查询分离，可以提高查询的可读性和维护性。特别是当主查询多次引用同一子查询时，WITH子句的优势更为明显。

WITH g AS
(select x.deptno
, avg(x.msal) avg_sal
from employees x
group by x.deptno)
select e.ename, e.init, e.msal
from employees e
join g
using (deptno)
where e.msal > g.avg_sal;
ENAME INIT MSAL
-------- ----- --------
ALLEN JAM 1600
JONES JM 2975
BLAKE R 2850
SCOTT SCJ 3000
KING CC 5000

FORD MG 3000

As you can see, we have isolated the subquery definition, in lines 1 through 5, from the actual query in lines 6 through 10. This makes the structure of the main query clearer. Using the WITH clause syntax becomes even more attractive if you refer multiple times to the same subquery from the main query. You can define as many subqueries as you like in a single WITH clause, separated by commas.

WITH v1 AS (select ... from ...)
, v2 AS (select ... from ...)
, v3 AS ...
select ...
from ...