如何将Runtime_Combined表中单日两条生产记录合并为单条输出记录

你当前编写的WHERE条件month(ProductionStartDate) = month (ProductionStartDate) 与 day(ProductionStartDate) = day( ProductionStartDate) 属于恒成立的判断，没有实际过滤效果，也无法实现同日两条记录合并为单条的需求。

你可以按生产时间的日期维度分组，取单日最早的生产开始时间、最晚的生产结束时间即可得到所需结果，不同数据库的实现语法略有差异：

• SQL Server 参考写法：

SELECT 
    CAST(ProductionStartDate AS DATE) AS ProductionDate,
    MIN(ProductionStartDate) AS ProductionStartDate,
    MAX(ProductionEndDate) AS ProductionEndDate
FROM Runtime_Combined
GROUP BY CAST(ProductionStartDate AS DATE)
ORDER BY ProductionDate

• MySQL 参考写法：

SELECT 
    DATE(ProductionStartDate) AS ProductionDate,
    MIN(ProductionStartDate) AS ProductionStartDate,
    MAX(ProductionEndDate) AS ProductionEndDate
FROM Runtime_Combined
GROUP BY DATE(ProductionStartDate)
ORDER BY ProductionDate

如果需要额外统计单日实际运行时长，可在SELECT部分加入对应时间差值计算函数即可，例如SQL Server中可以用DATEDIFF(MINUTE, MIN(ProductionStartDate), MAX(ProductionEndDate))得到当日运行总分钟数。

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