1-聚合函数|Aggregate Functions
- 聚合函数只运行非空值,所以如果列中有空值,它不会计算在函数里。如果想得到表格中所有记录条目,不论是否有空值:
COUNT(*) AS Total_records
- 函数后面可以接表达式:
SUM(invoice_total*1.1) AS Total
- 函数后面可以加筛选条件:
WHERE Invoice_date >’2019-07-01’
- 默认状态下,所有这些值会取重复值,如果要取不重复值需要加DISTINCT:
COUNT(DISTINCT client_id) AS Total_records
eg:
SELECT
‘First 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
‘Second 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-31’
以上查询得到:
2-GROUP BY字句|The GROUP BY Clause(当想知道每个client的销售额)
eg1:
以上查询得到:
- GROUP BY字句永远在FROM、WHERE之后。
eg2:
以上查询得到:
- 每个state和city组合我们得到一条记录。
eg3:
以上查询得到:
- AS前是列中的数据,AS后是标题。
3-HAVING字句|The HAVING Clause
以上查询得到:
而当只想筛选出Total_sales高于500美金的client时,由于WHERE优先于GROUP BY,此时不能用WHERE,我们用HAVING得以在分组之后筛选数据:
以上查询得到:
eg1:
以上查询得到:
要注意,HAVING字句后用到的列一定是SELECT字句中存在的,而WHERE后面可以接任何列,不论其是否存在于SELECT字句中。
eg2:
当SQL查询语句中除了聚合函数外选择了多个字段,在GROUP BY 的时候都要加上,道理很简单,如果只用多个字段中的一个字段作为分组的依据,那么剩下的字段仍然无法与聚合函数之间构成有明确合理的关联。
以上查询得到:
4-ROLLUP运算符|The ROLLUP Operator
WITH ROLLUP会让我们额外得到一行,汇总整个结果集。另外,ROLLUP只能用于聚合值的列。
eg1:
以上查询得到:
要注意,ROLLUP仅仅在MySQL中有,这不是一个标准的SQL语言,所以你无法在SQL Server或者Oracle里执行这个查询。
eg2: