Question

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 num
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 之后。