4 汇总统计4.1 聚合函数小结聚合函数输入一系列值并聚合为一个结果的函数实例USE sql_invoicing; ​ SELECT MAX(invoice_date) AS latest_date, -- SELECT选择的不仅可以是列也可以是数字、列间表达式、列的聚合函数 MIN(invoice_total) lowest, -- AS 可省略 AVG(invoice_total) average, SUM(invoice_total * 1.1) total, COUNT(*) total_records, COUNT(invoice_total) number_of_invoices, -- 和上一个相等 COUNT(payment_date) number_of_payments, -- 【聚合函数会忽略空值】得到的支付数少于发票数 COUNT(DISTINCT client_id) number_of_distinct_clients -- DISTINCT client_id 筛掉了该列的重复值再COUNT计数会得到不同顾客数 FROM invoices WHERE invoice_date 2019-07-01 -- 想只统计下半年的结果练习目标date_rangetotal_salestotal_paymentswhat_we_expect (the difference)1st_half_of_20192nd_half_of_2019TotalUSE sql_invoicing; ​ SELECT 1st_half_of_2019 AS date_range, SUM(invoice_total) AS total_sales, SUM(payment_total) AS total_payments, SUM(invoice_total - payment_total) AS what_we_expect FROM invoices WHERE invoice_date BETWEEN 2019-01-01 AND 2019-06-30 ​ UNION ​ SELECT 2st_half_of_2019 AS date_range, SUM(invoice_total) AS total_sales, SUM(payment_total) AS total_payments, SUM(invoice_total - payment_total) AS what_we_expect FROM invoices WHERE invoice_date BETWEEN 2019-07-01 AND 2019-12-31 ​ UNION ​ SELECT Total AS date_range, SUM(invoice_total) AS total_sales, SUM(payment_total) AS total_payments, SUM(invoice_total - payment_total) AS what_we_expect FROM invoices WHERE invoice_date BETWEEN 2019-01-01 AND 2019-12-314.2 GROUP BY子句小结按一列或多列分组注意语句的位置案例1按一个字段分组在发票记录表中按不同顾客分组统计下半年总销售额并降序排列USE sql_invoicing; ​ SELECT client_id, SUM(invoice_total) AS total_sales FROM invoices WHERE invoice_date 2019-07-01 -- 筛选过滤器 GROUP BY client_id -- 分组 ORDER BY total_sales DESC注在SELECT子句里出现的非聚合字段必须全部写到GROUP BY后面只有聚合函数SUM/COUNT/MAX/MIN/AVG不用进 GROUP BY即GROUP BY子句选择的字段是client_id是满足规则的。-- 错误invoice_total没做聚合又没放进GROUP BY SELECT client_id, invoice_total, SUM(invoice_total) FROM invoices GROUP BY client_id这里invoice_total既不是聚合又不在 GROUP BY直接报错新版 MySQL 默认严格模式不允许。另外注意ORDER BY后面不能用invoice_total排序分组后每行是客户总销售额没有单独的 invoice_total 明细。案例2按多个字段分组算各州各城市的总销售额USE sql_invoicing; ​ SELECT state, city, SUM(invoice_total) AS total_sales FROM invoices JOIN clients USING (client_id) GROUP BY state, city ORDER BY state这里GROUP BY后按正常理解其实去掉state语义上是没问题但由于MySQL 默认严格模式还是把字段都加上比较好。练习在 payments 表中按日期和支付方式分组统计总付款额USE sql_invoicing; ​ SELECT date, pm.name AS payment_method, SUM(amount) AS total_payments FROM payments p JOIN payment_methods pm ON p.payment_method pm.payment_method_id GROUP BY date, payment_method -- 用的是 SELECT 里的列别名 ORDER BY date4.3 HAVING子句小结HAVING 和 WHERE 都是是条件筛选语句条件的写法相通数学、比较包括特殊比较、逻辑运算都可以用如 AND、REGEXP 等等两者本质区别:WHERE 是对 FROM JOIN 里原表中的列进行 事前筛选所以WHERE可以对没选择的列进行筛选但必须用原表列名而不能用SELECT中确定的列别名相反 HAVING …… 对 SELECT …… 查询后通常是分组并聚合查询后的结果列进行 事后筛选若SELECT里起了别名的字段则必须用别名进行筛选且不能对SELECT里未选择的字段进行筛选。唯一特殊情况是当HAVING筛选的是聚合函数时该聚合函数可以不在SELECT里显性出现案例筛选出总发票金额大于500且总发票数量大于5的顾客USE sql_invoicing; ​ SELECT client_id, SUM(invoice_total) AS total_sales, COUNT(*/invoice_total/invoice_date) AS number_of_invoices FROM invoices GROUP BY client_id HAVING total_sales 500 AND number_of_invoices 5 -- 均为 SELECT 里的列别名若写WHERE total_sales 500 AND number_of_invoices 5会报错Error Code: 1054. Unknown column total_sales in where clause练习在 sql_store 数据库有顾客表、订单表、订单项目表等中找出在 VA 州且消费总额超过100美元的顾客这是一个面试级的问题还很常见思路需要的信息在顾客表、订单表、订单项目表三张表中先将三张表合并WHERE 事前筛选 VA 州的按顾客分组并选取所需的列并聚合得到每位顾客的付款总额HAVING 事后筛选超过 100美元 的USE sql_store; ​ SELECT c.customer_id, c.first_name, c.last_name, SUM(oi.quantity * oi.unit_price) AS total_sales FROM customers c JOIN orders o USING (customer_id) -- 别忘了括号特容易忘 JOIN order_items oi USING (order_id) WHERE state VA GROUP BY c.customer_id, c.first_name, c.last_name HAVING total_sales 1004.4 ROLLUP运算符GROUP BY …… WITH ROLL UP自动汇总型分组若是多字段分组的话汇总也会是多层次的注意这是MySQL扩展语法不是SQL标准语法案例分组查询各客户的发票总额以及所有人的总发票额USE sql_invoicing; SELECT client_id, SUM(invoice_total) FROM invoices GROUP BY client_id WITH ROLLUP多字段分组 例1分组查询各州、市的总销售额发票总额以及州层次和全国层次的两个层次的汇总额SELECT state, city, SUM(invoice_total) AS total_sales FROM invoices JOIN clients USING (client_id) GROUP BY state, city WITH ROLLUP多字段分组 例2分组查询特定日期特定付款方式的总支付额以及单日汇总和整体汇总USE sql_invoicing; SELECT date, pm.name AS payment_method, SUM(amount) AS total_payments FROM payments p JOIN payment_methods pm ON p.payment_method pm.payment_method_id GROUP BY date, pm.name WITH ROLLUP练习分组计算各个付款方式的总付款 并汇总SELECT pm.name AS payment_method, SUM(amount) AS total FROM payments p JOIN payment_methods pm ON p.payment_method pm.payment_method_id GROUP BY pm.name WITH ROLLUP总结各子句执行先后顺序GROUP BY 和其他子句的关系SQL 书写顺序SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT真实底层执行顺序关键决定各子句能做什么FROM找到要查询的表invoicesWHERE过滤原始行只保留invoice_date 2019-07-01的数据作用对象原始明细行不能用聚合结果过滤不能写WHERE SUM(xxx)GROUP BY用过滤完的数据按client_id分组生成各组聚合值SUM 等HAVING对分组后的聚合结果过滤比如HAVING SUM(invoice_total) 1000作用对象分组后的聚合结果只能过滤聚合后的数据SELECT最后挑选要展示的列分组字段 聚合函数ORDER BY对最终结果集排序LIMIT截取指定行数