SQL分组后如何获取最小、最大时间对应的关联字段值

可通过窗口函数实现需求，逻辑简洁性能更优，适配SQL Server、MySQL 8.0+、PostgreSQL等绝大多数主流数据库：

WITH daily_agg AS (
    SELECT  
        Name,
        CONVERT(date, StartDateAndTime) AS WorkDate,
        MIN(StartDateAndTime) OVER (PARTITION BY Name, CONVERT(date, StartDateAndTime)) AS StartTime,
        MAX(EndDateAndTime) OVER (PARTITION BY Name, CONVERT(date, StartDateAndTime)) AS EndTime,
        -- 提取当日最早出发时间对应的起始位置
        FIRST_VALUE(StartPositionText) OVER (
            PARTITION BY Name, CONVERT(date, StartDateAndTime) 
            ORDER BY StartDateAndTime ASC
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS StartPositionText,
        -- 提取当日最晚结束时间对应的结束位置
        FIRST_VALUE(EndPositionText) OVER (
            PARTITION BY Name, CONVERT(date, StartDateAndTime) 
            ORDER BY EndDateAndTime DESC
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS EndPositionText
    FROM tblWorkingTimes
)
SELECT DISTINCT Name, WorkDate, StartTime, EndTime, StartPositionText, EndPositionText
FROM daily_agg

低版本数据库兼容方案（无窗口函数支持场景） #

如果使用MySQL 5.x等不支持窗口函数的数据库，可通过子查询加关联匹配实现：

SELECT 
    base.Name,
    base.WorkDate,
    base.StartTime,
    base.EndTime,
    s.StartPositionText,
    e.EndPositionText
FROM (
    SELECT  
        Name,
        CONVERT(date, StartDateAndTime) AS WorkDate,
        MIN(StartDateAndTime) AS StartTime,
        MAX(EndDateAndTime) AS EndTime
    FROM tblWorkingTimes 
    GROUP BY Name, CONVERT(date, StartDateAndTime)
) base
-- 关联匹配最早出发时间对应的起始位置
LEFT JOIN tblWorkingTimes s 
    ON base.Name = s.Name 
    AND base.StartTime = s.StartDateAndTime
    AND base.WorkDate = CONVERT(date, s.StartDateAndTime)
-- 关联匹配最晚结束时间对应的结束位置
LEFT JOIN tblWorkingTimes e 
    ON base.Name = e.Name 
    AND base.EndTime = e.EndDateAndTime
    AND base.WorkDate = CONVERT(date, e.EndDateAndTime)

注意事项 #

• 若同一司机同一天存在多条完全相同的StartDateAndTime/EndDateAndTime记录，可根据业务需求调整排序规则或增加去重逻辑避免结果行数异常
• 部分数据库日期转换函数存在差异，可根据实际使用的数据库替换CONVERT(date, StartDateAndTime)为对应语法（如MySQL使用DATE(StartDateAndTime)）

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