MySQL 分组查询、排序与 LIMIT 用法详解

一、按时间维度分组查询

在处理日志、订单或用户行为数据时，通常需要将 create_time 等时间字段转换为指定的时间粒度。MySQL 提供了 DATE_FORMAT() 函数，可将日期格式化为字符串，随后结合 GROUP BY 进行统计。

第 1 步：定义时间格式占位符

DATE_FORMAT(date, format) 的第二个参数由特定的格式符组成，常用符号如下：

• %Y：四位年份（如 2022）；%y：两位年份（如 22）
• %m：数字月份（01-12）；%M：英文月份名（January）
• %d：数字日期（01-31）；%e：不带前导零的日期（1-31）
• %H：24小时制小时（00-23）；%h/%I：12小时制
• %U：周日为第一天的第几周；%u：周一为第一天的第几周
• %i：分钟；%s/%S：秒

例如：'%Y-%m-%d' 输出 2022-11-22，'%Y%m%d' 输出 20221122。

第 2 步：执行不同粒度的分组统计

假设数据表 user 包含 id, name, age, birthday, gender, create_time 字段。以下是按不同时间粒度分组的 SQL 写法：

1. 按“日”分组并过滤时间范围

SELECT DATE_FORMAT(create_time,'%Y-%m-%d') AS days, COUNT(id) AS cnt 
FROM user 
WHERE create_time >= '2022-11-11 00:00:00' 
  AND create_time < '2022-12-01 00:00:00' 
GROUP BY days
ORDER BY days DESC;

注意：比较时间字符串时，建议使用 >= 和 < 而非 BETWEEN，以避免边界时刻（如 23:59:59.999）的遗漏。

2. 按“周”分组

SELECT DATE_FORMAT(create_time,'%Y-%u') AS weeks, COUNT(id) 
FROM user 
GROUP BY weeks;

这里使用 %u 代表以周一为一周开始的标准周数。

3. 按“月”分组

SELECT DATE_FORMAT(create_time,'%Y-%m') AS months, COUNT(id) 
FROM user 
GROUP BY months;

4. 按“年”分组

SELECT DATE_FORMAT(create_time,'%Y') AS years, COUNT(id) 
FROM user 
GROUP BY years;

修正提示：原草稿中别名使用了 months，正确应为 years 或直接省略别名。此处修正为规范写法。

5. 按“小时”分组

SELECT DATE_FORMAT(create_time,'%Y-%m-%d %H') AS hours, COUNT(id) 
FROM user 
WHERE create_time >= '2022-11-11 00:00:00' 
  AND create_time < '2022-12-01 00:00:00' 
GROUP BY hours;

二、MySQL 数据排序（ORDER BY）

查询结果的默认排序取决于引擎实现，通常为插入顺序。为了业务逻辑展示，需显式指定排序规则。

1. 基本语法规则

• ASC：升序（Ascending），默认选项。
• DESC：降序（Descending）。
• ORDER BY 子句必须位于 SELECT 语句的最后，通常在 WHERE、GROUP BY 之后，且排在 LIMIT 之前。

2. 常见排序场景

单列排序

查询所有用户，按年龄从大到小排列：

SELECT * FROM user ORDER BY age DESC;

带条件的排序

查询年龄大于 20 的用户，并按出生日期从小到大（升序）排列：

SELECT * 
FROM user 
WHERE age > 20 
ORDER BY birthday ASC;

重要顺序：WHERE 过滤行，ORDER BY 整理剩余行的顺序。二者共存时，必须先写 WHERE，再写 ORDER BY。

3. 多列排序（扩展补充）

若主键相同，可按次要字段排序：

SELECT * FROM user ORDER BY age DESC, id ASC;

表示先按 age 降序，age 相同时按 id 升序。

三、LIMIT 限制返回行数与分页

LIMIT 子句用于控制查询结果的数量，常用于避免返回海量数据导致内存溢出或提升前端加载速度。

1. 基础语法结构

SELECT column_list FROM table_name LIMIT offset, rows;

• offset：起始偏移量。第一条记录的 offset 为 0。可选参数，若省略则默认为 0。
• rows：要返回的记录行数。必选参数（若写两个参数）。

2. 简单用例

• 获取前 10 条记录：SELECT * FROM user LIMIT 10; （等同于 limit 0, 10）
• 跳过前 5 条，取接下来的 10 条（即第 6 到 15 条）：SELECT * FROM user LIMIT 5, 10;

3. 应用：后端分页计算

在前端请求中，通常传入当前页码 page（从 1 开始）和每页大小 pageSize。对应的 MySQL 查询公式推导如下：

• 第 1 页（page=1）：取 0 开始的 pageSize 条 → (1-1)*pageSize, pageSize
• 第 2 页（page=2）：跳过 1*pageSize 条，取 pageSize 条 → (2-1)*pageSize, pageSize
• N 页：跳过 (N-1)*pageSize 条 → ((page-1)*pageSize), pageSize

通用分页 SQL 模板：

SELECT * FROM table_name LIMIT (page-1)*pageSize, pageSize;

四、IFNULL 处理 NULL 值与数学运算

在数据库设计中，字段允许为 NULL。但在进行加减乘除或求和统计时，任何涉及 NULL 的算术运算结果均可能变为 NULL，导致数据展示为空。此时需使用 IFNULL() 将 NULL 替换为默认值（如 0）。

1. 函数解释

IFNULL(value1, value2)：

• 如果 value1 不为 NULL，返回 value1。
• 如果 value1 为 NULL，返回 value2。

2. 实战场景：分数统计

假设表 score 包含 id, Chinese, English, Physics，其中部分物理成绩缺失（NULL）：

场景 A：总和统计中的判空

直接 SUM(Physics) 会忽略 NULL 值（正常），但若要求显示为 0 而非 NULL，或者在某些不支持隐式转换的环境中：

-- 安全写法：如果没有记录或全为null，返回0
SELECT IFNULL(SUM(Physics), 0) FROM score;

场景 B：字段间的算术运算

计算某学生的语文和物理总分。由于物理为 NULL，直接相加结果为 NULL：

-- 错误：返回 NULL
SELECT Chinese + Physics FROM score WHERE id = 1;

正确做法是将参与的字段分别用 IFNULL 包裹，将缺失值视为 0：

-- 正确：返回 99 + 0 = 99
SELECT IFNULL(Chinese, 0) + IFNULL(Physics, 0) AS total_score 
FROM score 
WHERE id = 1;

对比：SUM 聚合函数的特性

SUM() 作为聚合函数，会自动忽略 NULL 行。例如 SUM(Chinese + Physics) 也会因为加法运算中的 NULL 导致整行为 NULL，因此最好在聚合前处理单个字段的 NULL，或使用 COALESCE(Chinese, 0) + COALESCE(Physics, 0)。

总结

本文梳理了 MySQL 中最核心的四个操作模块：利用 DATE_FORMAT 灵活实现多维度分组，使用 ORDER BY 规范数据呈现顺序，掌握 LIMIT 实现高效的翻页机制，并通过 IFNULL 确保数值计算的准确性。实际开发中，可根据业务需求自由组合这些语法。以上就是 MySQL 按年月日分组、排序及判空用法的详细内容，更多关于 MySQL 高级查询技巧的资料请关注本站其它相关文章！