按日期分组提取length字段最小最大值并分行展示问题

现有包含length和日期字段的数据表，具体数据如下：

期望按日期的日期部分分组，提取每组中length字段的最小值、最大值对应的记录并分行展示，结果如下：

目前尝试的SQL语句仅能提取全局的最小最大值，无法按日期分组：

SELECT * FROM t
WHERE length
IN (SELECT MIN(length) FROM t), (SELECT MAX(length) FROM t));

要实现按日期分组取每组的最小、最大length记录，有两种常用方案：

方案一：使用窗口函数（推荐，适用于支持SQL:2003及以上的数据库，如MySQL 8.0+、PostgreSQL、SQL Server等） #

利用ROW_NUMBER()或RANK()窗口函数，按日期的日期部分分组，分别对每组内的length升序、降序排序，取排序后第一条的记录：

WITH ranked_data AS (
    SELECT 
        *,
        -- 按日期分组，length升序排，标记每组最小length的记录
        ROW_NUMBER() OVER (PARTITION BY DATE(日期) ORDER BY length ASC) AS rn_min,
        -- 按日期分组，length降序排，标记每组最大length的记录
        ROW_NUMBER() OVER (PARTITION BY DATE(日期) ORDER BY length DESC) AS rn_max
    FROM t
)
SELECT length, 日期
FROM ranked_data
WHERE rn_min = 1 OR rn_max = 1
ORDER BY DATE(日期), length;

如果同一组内存在多条相同的最小/最大length记录，ROW_NUMBER()会只取其中一条，若想保留所有相同值的记录，可替换为RANK()。

方案二：关联子查询（兼容低版本数据库） #

先通过子查询获取每个日期分组的最小、最大length值，再将原表与这些值关联：

SELECT t.*
FROM t
JOIN (
    SELECT 
        DATE(日期) AS date_part,
        MIN(length) AS min_len,
        MAX(length) AS max_len
    FROM t
    GROUP BY DATE(日期)
) AS group_stats 
ON DATE(t.日期) = group_stats.date_part 
AND (t.length = group_stats.min_len OR t.length = group_stats.max_len)
ORDER BY DATE(t.日期), t.length;

说明 #

• DATE(日期)函数用于提取datetime字段的日期部分，不同数据库的函数可能有差异：
  • MySQL: DATE(日期)
  • PostgreSQL: DATE(日期) 或 CAST(日期 AS DATE)
  • SQL Server: CAST(日期 AS DATE)
  • Oracle: TRUNC(日期)

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