1、DISTINCT关键字
DISTINCT用于筛选相同值只显示一次,如:
COUNT(client_id) AS total_records
此处应为有client_id相同的值都计入在内,我们为了每个id只计数一次:
COUNT(DISTINCT client_id) AS total_records
2、HAVING
HAVING子句可以在我们分组之后(生成的列)筛选数据,且HAVING之后的条件必须在SELECT子句中出现;
WHERE子句可以在我们分组之前(现有的列)筛选数据;
两者都是可以用AND连接多个条件;
3、ROLLUP
ROLLUP运算符只能应用于聚合类的列(如sum())
SELECT
state,
city,
SUM(invoice_total) AS total_sales
FROM invoices i
JOIN clients c USING (client_id)
GROUP BY state,city WITH ROLLUP
ROLLUP对每个州各个城市进行了一个汇总,最后所有州也进行了汇总。
注:ROLLUP只在mysql中有效,其他数据库引擎如sql server、Oracle无效,但也有其他方式。
4、GROUP BY后面不能用列别名,如:
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要用pm.name而不能用payment_method。