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
- ROUND
SELECT ROUND(5.5343,2)
##保留两位小数（四舍五入)，得到5.53
- TRUNCATE
SELECT TRUNCATE(5.5343,2)
##保留两位小数（直接删去后面的数字），得到5.53
- CEILING
SELECT CEILING(5.5343)
##得到比其大的最近整数，返回6
- FLOOR
SELECT FLOOR(5.5343)
##得到比其小的最近整数，返回5
- ABS
SELECT ABS(-5.5343)
##得到绝对值，返回5.5343
- RAND
SELECT RAND()
##返回一个random value between 0 to 1
- 更多的info 可以搜索 mySQL numeric function
