0%

3.8.5 from子句中的子查询

发表于 更新于 分类于 ， ， ， Valine：


3.8.5 from子句中的子查询

SQL允许在from子句中使用子查询表达式。因为任何select-from-where表达式返回的结果都是关系,所以子查询可以被插入到另一个select-from- where中任何关系可以出现的位置。

SQL查询 找出系平均工资超过42000美元的那些系中教师的平均工资

考虑査询”找出系平均工资超过42000美元的那些系中教师的平均工资“。在3.7节我们使用了having子句来书写此查询。现在我们可以不用having子句来重写这个查询,而是通过如下这种在from子句中使用子查询的方式:

1
2
3
4
5
6
select dept_name,avg_salary
from ( select dept_name, avg(salary) as avg_salary
    from instructor 
    group by dept_name
) as I
where avg_salary>42000;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> select dept_name,avg_salary
from ( select dept_name, avg(salary) as avg_salary
    from instructor 
    group by dept_name
) as I
where avg_salary>42000;
+------------+--------------+
| dept_name  | avg_salary   |
+------------+--------------+
| Biology    | 72000        |
| Comp. Sci. | 77333.333333 |
| Elec. Eng. | 80000        |
| Finance    | 85000        |
| History    | 61000        |
| Physics    | 91000        |
+------------+--------------+
6 rows in set

子查询:
1
2
3
4
( select dept_name, avg(salary) as avg_salary
    from instructor 
    group by dept_name
) as I

产生的关系包含所有系的名字和相应的教师平均工资。子查询的结果属性可以在外层查询中使用.

注意我们不需要使用having子句,因为from子句中的子查询计算出了每个系的平均工资,早先在having子句中使用的谓词现在出现在外层查询的where子句中。

重命名from子查询的结果关系

我们可以用as子句重命名子查询的结果关系,也可以使用as子句重命名子查询的属性

1
2
3
4
5
6
select dept_name,avg_salary
from( select dept_name, avg(salary) as avg_salary
    from instructor 
    group by dept_name
) as dept_avg
where avg_salary > 42000;

数据库实现对from子查询的支持

很多(但并非全部)SQL实现都支持在from子句中嵌套子查询。
请注意,某些SQL实现要求对每一个from子查询结果关系都给一个名字,即使该名字从不被引用;(MySQL)

MySQL必须给from自己的子查询取别名

经过我的测试MySQL必须对from子查询的结果关系取别名,不给子查询取别名会报错:Every derived table must have its own alias：

1
2
3
4
5
6
7
mysql> select dept_name,avg_salary
from( select dept_name, avg(salary) as avg_salary
    from instructor 
    group by dept_name
)
where avg_salary > 42000;
1248 - Every derived table must have its own alias

SQL查询 找出在所有系中工资总额最大的系

作为另一个例子,假设我们想要找出在所有系中工资总额最大的系。在此having子句是无能为力的,但我们可以用from子句中的子查询轻易地写出如下查询

1
2
3
4
5
6
select max(tot_salary)
from(
    select dept_name, sum(salary) as tot_salary
        from instructor
        group by dept_name
)as dept_total;

1
2
3
4
5
6
7
8
9
10
11
12
select max(tot_salary)
from(
    select dept_name, sum(salary) as tot_salary
        from instructor
        group by dept_name
)as dept_total;
+-----------------+
| max(tot_salary) |
+-----------------+
| 232000.00       |
+-----------------+
1 row in set

我们注意到在from子句嵌套的子查询中不能使用来自from子句其他关系的相关变量。

子查询访问外层查询的相关变量 lateral关键词

然而SQL2003允许from子句中的子查询用关键词lateral作为前缀,以便访问from子句中在它前面的表或子查询中的属性。
例如,如果我们想打印每位教师的姓名,以及他们的工资和所在系的平均工资,可书写查询如下:

1
2
3
4
5
6
select name, salary, avg_salary
from instructor I1,lateral(
    select avg(salary) as avg_salary
    from instructor I2
    where I1.dept_name=I2.dept_name
);

没有lateral子句的话,子查询就不能访问来自外层查询的相关变量I1。

数据库实现支持

目前只有少数SQL实现支持lateral子句,比如IBM DB2