如何在MariaDB中查询指定日期范围内每日的首末条记录

方法1：使用窗口函数（MariaDB 10.2+/MySQL 8.0+） #

这种方法简洁高效，利用窗口函数按日期分组排序，直接筛选每日的第一条和最后一条记录：

SELECT *
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY date_recorded ORDER BY time_recorded ASC) AS rn_asc,
        ROW_NUMBER() OVER (PARTITION BY date_recorded ORDER BY time_recorded DESC) AS rn_desc
    FROM readings
    WHERE date_recorded > '2022-01-01' 
      AND date_recorded < '2022-01-15' 
      AND time_recorded > '08:00:00' 
      AND time_recorded < '17:00:00'
) AS ranked
WHERE rn_asc = 1 OR rn_desc = 1
ORDER BY date_recorded, time_recorded;

说明： #

• 内层查询通过PARTITION BY date_recorded将数据按日期分组，分别按time_recorded升序、降序生成行号
• 外层筛选行号为1的记录，即每日的最早（rn_asc=1）和最晚（rn_desc=1）读数
• 最终按日期和时间排序，方便查看结果

方法2：分组关联法（兼容旧版本MariaDB/MySQL） #

如果数据库版本不支持窗口函数，可先分组获取每日的最小/最大时间，再关联原表取出对应记录：

-- 获取每日最早读数
SELECT r.*
FROM readings r
JOIN (
    SELECT date_recorded, MIN(time_recorded) AS min_time
    FROM readings
    WHERE date_recorded > '2022-01-01' 
      AND date_recorded < '2022-01-15' 
      AND time_recorded > '08:00:00' 
      AND time_recorded < '17:00:00'
    GROUP BY date_recorded
) AS min_times ON r.date_recorded = min_times.date_recorded AND r.time_recorded = min_times.min_time

UNION ALL

-- 获取每日最晚读数
SELECT r.*
FROM readings r
JOIN (
    SELECT date_recorded, MAX(time_recorded) AS max_time
    FROM readings
    WHERE date_recorded > '2022-01-01' 
      AND date_recorded < '2022-01-15' 
      AND time_recorded > '08:00:00' 
      AND time_recorded < '17:00:00'
    GROUP BY date_recorded
) AS max_times ON r.date_recorded = max_times.date_recorded AND r.time_recorded = max_times.max_time

ORDER BY date_recorded, time_recorded;

说明： #

• 两个子查询分别按日期分组，计算每日的最小/最大时间
• 通过JOIN关联原表，取出对应时间的完整记录
• 使用UNION ALL合并结果（避免去重，每日首末记录不会重复）
• 最后按日期和时间排序

内容的提问来源于stack exchange，提问作者Apolymoxic