MySQL 按时间查最大和最小的两条数据

在数据库管理中，提取特定时间范围内的最大值和最小值是一个非常常见的需求。在这篇文章中，我们将探讨如何使用 MySQL 查询语句来查找某个时间段内最大和最小的两条数据。我们将通过一个示例来说明整个流程，并提供相应的代码示例。

设计数据表

首先，我们需要创建一个示例数据表，包含时间戳和数据值。假设我们有一个记录温度变化的表格，命名为 temperature_records，其中包含字段 id、recorded_at（记录时间）和 temperature（温度值）。

CREATE TABLE temperature_records (
    id INT AUTO_INCREMENT PRIMARY KEY,
    recorded_at DATETIME NOT NULL,
    temperature DECIMAL(5, 2) NOT NULL
);

我们将向该表中插入一些示例数据：

INSERT INTO temperature_records (recorded_at, temperature) VALUES
('2023-10-01 08:00:00', 25.5),
('2023-10-01 09:00:00', 26.0),
('2023-10-01 10:00:00', 26.5),
('2023-10-01 11:00:00', 24.5),
('2023-10-01 12:00:00', 23.0);

查询最大和最小值的流程

流程图

在开始编写 SQL 查询语句之前，我们首先可以使用一个流程图来描述整个逻辑。

flowchart TD
    A[开始] --> B{选择时间范围}
    B -->|选择范围| C[查询数据]
    C --> D{找到最大温度}
    C --> E{找到最小温度}
    D --> F[显示最大温度的记录]
    E --> G[显示最小温度的记录]
    F --> H[结束]
    G --> H

查询最大和最小记录

在 MySQL 中，我们可以使用子查询来获取某个时间范围的最大和最小记录。例如，想要查询 2023年10月1日 08:00 到 2023年10月1日 12:00 的温度记录，可以使用如下 SQL 语句：

SELECT *
FROM temperature_records
WHERE recorded_at BETWEEN '2023-10-01 08:00:00' AND '2023-10-01 12:00:00'
ORDER BY temperature DESC LIMIT 1;

SELECT *
FROM temperature_records
WHERE recorded_at BETWEEN '2023-10-01 08:00:00' AND '2023-10-01 12:00:00'
ORDER BY temperature ASC LIMIT 1;

上述 SQL 语句的具体含义为：

  1. 首先，我们从 temperature_records 表中选择在指定时间范围内的所有记录。
  2. 使用 ORDER BY temperature DESC LIMIT 1 查询温度最高的记录。
  3. 同样，使用 ORDER BY temperature ASC LIMIT 1 查询温度最低的记录。

整合查询

为了方便处理，可以将其整合到一个查询中，通过联合查询得到最大和最小的记录。

SELECT *
FROM (
    SELECT *
    FROM temperature_records
    WHERE recorded_at BETWEEN '2023-10-01 08:00:00' AND '2023-10-01 12:00:00'
    ORDER BY temperature DESC LIMIT 1
) AS max_temp

UNION ALL

SELECT *
FROM (
    SELECT *
    FROM temperature_records
    WHERE recorded_at BETWEEN '2023-10-01 08:00:00' AND '2023-10-01 12:00:00'
    ORDER BY temperature ASC LIMIT 1
) AS min_temp;

关系图示

为更好地理解这张数据表与其结构之间的关系，我们可以使用 ER 图来表示。

erDiagram
    TEMPERATURE_RECORDS {
        int id PK "主键"
        datetime recorded_at "记录时间"
        decimal temperature "温度值"
    }

结论

通过以上流程，我们详细探讨了如何在 MySQL 中按时间查找最大和最小的两条数据记录。在实际业务中，这一方法可以广泛应用于气温监测、销售数据分析、网站访问量统计等多个场景中。我们希望这篇文章不仅能帮助你理解如何操作 MySQL 查询，更能使你对数据处理的思路有更深入的认识和掌握。