3.8.6 with子句

发表于 更新于 分类于 ， ， ， Waline： 本文字数： 1.6k 阅读时长 ≈ 1 分钟

3.8.6 with子句

with子句提供定义临时关系的方法,这个定义只对包含with子句的查询有效。

SQL查询 找出具有最大预算值的系

考虑下面的查询,它找出具有最大预算值的系。

1
2
3
4
5
6
7
with max_budget(value) as (
    select max(budget)
    from department
)
select budget
from department, max_budget
where department.budget=max_budget.value;
1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> with max_budget(value) as (
    select max(budget)
    from department
)
select budget
from department, max_budget
where department.budget=max_budget.value;
+--------+
| budget |
+--------+
| 120000 |
+--------+
1 row in set

我们也能用from子句或where子句中的嵌套子查询书写上述查询。但是,用嵌套子查询会使得查询语句晦涩难懂。with子句使査询在逻辑上更加清晰,它还允许在一个查询内的多个地方使用视图定义。
例如,假设我们要查出所有工资总额大于所有系平均工资总额的系,我们可以利用如下with子句写出查询:

1
2
3
4
5
6
7
8
9
10
11
with dept_total(dept_name,value) as (
    select dept_name, sum(salary)
    from instructor
    group by dept_name
),
dept_total_avg(value) as (
    select avg(value) from dept_total
)
select dept_name
from dept_total,dept_total_avg
where dept_total.value>=dept_total_avg.value;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
mysql> with dept_total(dept_name,value) as (
    select dept_name, sum(salary)
    from instructor
    group by dept_name
),
dept_total_avg(value) as (
    select avg(value) from dept_total
)
select dept_name
from dept_total,dept_total_avg
where dept_total.value>=dept_total_avg.value;
+------------+
| dept_name  |
+------------+
| Comp. Sci. |
| Finance    |
| Physics    |
+------------+
3 rows in set

我们当然也可以不用with子句来建立等价的查询,但是那样会复杂很多,而且也不易看懂.