My SQL 排序和分组

排序和分组

排序

​ ORDER BY 用于对结果集进行排序，可以按多个列进行排序，并且为每个列指定不同的排序方式。

• ASC ：升序（默认）
• DESC ：降序

--指定多个列的排序方向
SELECT * FROM products
ORDER BY prod_price DESC, prod_name ASC;

分组

​ 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 可以在相同的查询中。

HAVING vs WHERE？

• WHERE 和 HAVING 都是用于过滤。
• HAVING 适用于汇总的组记录；而 WHERE 适用于单个记录。

--使用 WHERE 和 HAVING 过滤数据
SELECT cust_name, COUNT(*) AS num
FROM Customers
WHERE cust_email IS NOT NULL
GROUP BY cust_name
HAVING COUNT(*) >= 1;