Mysql笔记：group by、having和where解析

Mysql笔记：group by、having和where解析

group by：

group by 子句将记录分组到汇总行中。

group by 为每个组返回一个记录。

group by 通常还涉及聚合count，max，sum，avg 等。

group by 可以按一列或多列进行分组。

group by 按分组字段进行排序后，order by 可以以汇总字段来进行排序。

--分组

SELECT cust_name, COUNT(cust_address) AS addr_num FROM Customers GROUP BY cust_name; 

--分组后排序

SELECT cust_name, COUNT(cust_address) AS addr_num FROM Customers GROUP BY cust_name ORDER BY cust_name DESC; 

having：

having 用于对汇总的 group by 结果进行过滤。

having 一般都是和 group by 连用。

where 和 having 可以在相同的查询中。

-- 使用 WHERE 和 HAVING 过滤数据

SELECT cust_name, COUNT(*) AS NumberOfOrders FROM Customers WHERE cust_email IS NOT NULL GROUP BY cust_name HAVING COUNT(*) > 1; 

having vs where：

where：过滤过滤指定的行，后面不能加聚合函数（分组函数）。

where 在group by 前。

having：过滤分组，一般都是和 group by 连用，不能单独使用。

having 在 group by 之后。