1,在第九节最后一道练习中,我们写了一段查询,出现了一个表,现在我们可以把这个表也当做一个子查询,用from连接,但仅限于简单查询。如下
select *
from(
select
client_id,
name,
(select sum(invoice_total)
from invoices
where client_id=c.client_id) as total_sales,
(select avg(invoice_total) from invoices) as average,
(select total_sales-average) as difference
from clients c
) as sales_summary
这里as sales_summary是一定要的,也就是一定要给个别名
2,执行查询之后看似还是之前的表,但是我们可以用这张表做筛选,分组等等操作
比如:where total_sales is not null
select *
from(
select
client_id,
name,
(select sum(invoice_total)
from invoices
where client_id=c.client_id) as total_sales,
(select avg(invoice_total) from invoices) as average,
(select total_sales-average) as difference
from clients c
) as sales_summary
where total_sales is not null
还可以join其他表等等
3,可以用from下的子查询作为视图,如上蓝色字体部分,存储在数据库中,命名为sales_summary,会简化查询,之后课程会说到