阿狗工具

1. 基础

• group by 对结果集进行分组，在分组的列上可以使用聚合函数（avg、sum、max、min、count）
• group by 必须在 where 之后 order by 之前
• having 子句可应用限定条件进行分组 group by ... having ...

基本语法

SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

2. 分组 GROUP BY

• group by field group by 后面的字段必须是某一个列或者某个列的聚合函数
• 不在 group by 中的列不能直接作为返回列放在sql语句中

SELECT user_name, MAX(quantity) AS MAXIMUM
FROM boot_order
GROUP BY user_name

3. 分组筛选：HAVING

3.1 HAVING 后面的表达式

• 常数
• 聚合函数
• GROUP BY 指定的列名（即聚合键）

3.2 where vs having

• where 和 having 都是筛选条件，但 where 在 group by 前， having 在 group by 之后
• 聚合函数（avg、sum、max、min、count），不能作为条件放在where之后，但可以放在having之后
• where 后面不能用聚合函数
• having 后面可用聚合函数

3.3 示例 SQL

SELECT user_name, MAX(quantity) AS MAXIMUM, COUNT(quantity)
FROM boot_order
GROUP BY user_name
HAVING COUNT(*) > 1

SELECT user_name, MAX(quantity) AS MAXIMUM
FROM boot_order
GROUP BY user_name
HAVING AVG(quantity) > 1

4. 练习：按时间分组

计算出分钟数，相同时间为一组
例：2022-01-01 12:44:30 => 12*60 + 44 = 720 + 44 = 764

SELECT (hour(gmt_create) * 60 + minute(gmt_create)) as minutes,
count(distinct code) as count
FROM boot_order
where quantity > 0
group by minutes
order by minutes