第六章 9-select子句中的子查询|subqueries in the select clause--Mosh my SQL

1,如何写出这张表的查询

答案

2，练习

答案：

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

解题思路：

1，secelt 对应列 +from clients (因为这里client_id,name都来自clients)

2，接下来是select的剩余部分

(1). (select sum(invoice_total)  from invoices where client_id=c.client_id) as total_sales,

as前代表invoices里面顾客的invoices总和,as 之后命名

(2).(select avg(invoice_total) from invoices) as average,

select from 组合不变，然后选择invoice总和的平均数

(3). (select total_sales-average) as difference，

选择这两的差作为 difference ,如果没有select会出错：

Error Code: 1054. Unknown column 'total_sales' in 'field list'