47 Subqueries in the SELECT Clause

• subquery可以用在WHERE 也可以用在SELECT里

SELECT 
    client_id,
    name,
    (SELECT SUM(invoice_total)
    FROM invoices
    WHERE c.client_id = client_id) AS total_sales,
    (SELECT AVG(invoice_total)
    FROM invoices) AS average,
    (SELECT total_sales - average) AS difference
FROM clients c    
## 输出client的name，总销售额，个人平均销售额以及二者的差值

• 不过话说，用个人总sales与平均每个invoice的sales 做差值，感觉毫无意义呀。

SELECT 
    client_id,
    name,
    (SELECT AVG(invoice_total)
    ## 将SUM 改为 AVG
    FROM invoices
    WHERE c.client_id = client_id) AS total_sales,
    (SELECT AVG(invoice_total)
    FROM invoices) AS average,
    (SELECT total_sales - average) AS difference
FROM clients c    

• 这样才好点吧

---

• 一个小疑惑点。

• 在SElECT subquery 中，不能直接使用 AS 后面的 alias作为运算的成分

即需要书写

 (SELECT total_sales - average) AS difference

而不能写成

total_sales - average AS difference
## 直接对SELECT的column 进行计算就可以直接引用

48 Subqueries in the FROM Clause

• 在使用FROM 从句是，需要给从句一个alias

SELECT *
FROM (
    SELECT 
        client_id,
        name,
        (SELECT AVG(invoice_total)
        FROM invoices
        WHERE c.client_id = 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
##输出client的name，个人平均销售额，总平均销售额以及二者的差值，并排除无sales的client

• ps:可以用view操作更好解决。

49 Numeric Functions

• Some very useful numeric functions

1. ROUND

SELECT ROUND(5.5343,2)
##保留两位小数（四舍五入)，得到5.53

2. TRUNCATE

SELECT TRUNCATE(5.5343,2)
##保留两位小数（直接删去后面的数字），得到5.53

3. CEILING

SELECT CEILING(5.5343)
##得到比其大的最近整数，返回6

4. FLOOR

SELECT FLOOR(5.5343)
##得到比其小的最近整数，返回5

5. ABS

SELECT ABS(-5.5343)
##得到绝对值，返回5.5343

6. RAND

SELECT RAND()
##返回一个random value between 0 to 1

• 更多的info 可以搜索 mySQL numeric function