Sql学习笔记4——嵌套子查询（下）

5）from子句中的子查询

因为SQL任何select-from-where表达式返回的都是关系，所以from子句中允许使用子查询表达式。

考虑查询“找出平均工资超过42000美元的那些系中教师的平均工资”，之前我们用having子句来书写该查询。

现在我们不用having子句，如下：

　　select dept_name,avg_salary

　　from(select dept_name,avg(salary) as avg_salary

　　　　from instructor

　　　　group by dept_name)

　　where avg_salary>42000;

我们还可以用 as子句给此子查询结果关系起个名字，并对属性进行重命名。如下：

　　select dept_name, avg_salary

　　from(select dept_name,avg(salary)

　　　　from instructor

　　　　group by dept_name)

　　　　as dept_avg(dept_name,avg_salary)　　/*as常用场合*/

　　where avg_salary>42000;

另一例子，假设我们要找出所有系中工资总额最大的系，在此having子句则无能为力。但from嵌套子查询可以轻易写出：

　　select max（tot_salary)

　　from(select dept_name,sum(salary)

　　　　from instructor

　　　　group by dept_name)as dept_total(dept_name,tot_salary);

注：并非全部sql实现都支持在from子句中嵌套子查询。

from子句嵌套不允许使用来自from子句其他关系的相关变量。

6）with子句

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

考虑查询“找出具有最大预算值的系”。如下

　　with max_budget(value) as

　　　　(select max(budget)

　　　　from department)

　　select budget

　　from department,max_budget

　　where department.budget=max_budget.value;

with子句定义了临时关系max_budget，随后被使用。

7）标量子查询

sql允许子查询出现在返回单个值的表达式能够出现的任何地方，只要该子查询只返回单个属性的单个元组，称为标量子查询。它可以出现在select、where和having子句中。

考虑查询“列出所有的系以及它们拥有的教师数：

　　select dept_name,(select count(*)

　　　　　　　　　　　　from instructor

　　　　　　　　　　　　where department.dept_name = instructor.dept_name)

　　　　　　　　　　　　as num_instructors

　　from department;