MySQL（三）

目录

1、聚合函数

         2、GROUP BY 子句

         3、HAVING子句

         4、ROLLUP 运算符

---

1、聚合函数

MAX(),MIN(),AVG(),SUM(),COUNT()
(只运算非空值)
SELECT
MAX(invoice_total) AS highest,    (最大值可以查出最近的日期)
MIN(invoice_total) AS lowest,
AVG(invoice_total) AS average,
SUM(invoice_total * 1.1) AS total,    （每个值都乘完后算和）
COUNT( DISTINCT invoice_total) AS num_of_invoices,   (去重)
FROM invoice
WHERE data = '1999-04-12'

 2、GROUP BY 子句

（分组）
SELECT 
    client_id
    SUM(invoice_total) AS total_sales
FROM invoices
GROUP BY client_id          （分组）（GROUP BY  永远在FROM 和 WHERE 之后 ORDER BY 之前）
ORDER BY total_sales DESC   (倒序)

3、HAVING子句

（HAVING和WHERE的区别就是 ：
                            WHERE只能在分组前进行筛选，HAVING可以在分组后进行筛选
                            WHERE可以使用任何列作为条件，HAVING只能使用SELECT子句选中的列
）
SElECT
    client_id
    SUM(invoice_total) AS toatl_sales,
    count(*) AS number_of_invoices
FROM invoices
GROUP BY client_id
HAVING total_sales > 500 AND number_of_invoices > 5

4、ROLLUP 运算符

（求每一个分组的聚合函数的和，和计算所有分组聚合函数的和）
（ROLLUP不可以使用别名）
SELECT
    state,
    city, 
    SUM(invoice_total) AS total_sales
FROM invoices i
JOIN client c USING (client_id)
GROUP BY state,city WITH ROLLUP