双表关联计算汽车部件使用时间段内总时长的SQL查询问题及解决方案

首先明确你的需求：要计算每个汽车部件在其安装后的有效使用时间段内的总时长，比如部件ID为1的示例中，总时长应为1:20:00。

数据表说明 #

先回顾下两张核心表的结构：

Table1（存储部件安装信息） #

Table2（存储部件使用时间信息） #

两张表通过car_id关联，需要注意的是Table2里存在无效数据（比如id=4的记录，putoff_time早于puton_time），这部分需要过滤掉。

你原查询的问题 #

你写的SQL存在几个关键问题：

• 主表选择错误：应该以部件安装表（Table1）为主，而不是使用记录表（Table2），否则会丢失未产生使用记录的部件。
• 字段与逻辑错误：引用了不存在的字段datum，且子查询的时间范围判断逻辑无法正确匹配部件的有效使用时段。
• 未处理无效时间差：没有考虑putoff_time早于puton_time的情况，会导致负的时长值。

正确的SQL解决方案 #

这里提供修正后的查询语句，能够准确计算目标结果：

SELECT 
    t1.part_id,
    SEC_TO_TIME(SUM(ABS(TIME_TO_SEC(TIMEDIFF(t2.puton_time, t2.putoff_time))))) AS total_time
FROM table1 t1
LEFT JOIN table2 t2 
    ON t1.car_id = t2.car_id 
    AND t2.run_date >= t1.part_date
    AND t2.putoff_time > t2.puton_time -- 过滤无效的使用时段
WHERE t1.part_id = 1 -- 若要查询所有部件，可移除该WHERE条件
GROUP BY t1.part_id;

关键逻辑说明： #

1. 关联方向调整：以Table1为主表，确保每个部件都能被统计到，即使没有使用记录（此时总时长为NULL）。
2. 有效时间范围过滤：通过t2.run_date >= t1.part_date，只统计部件安装之后的使用记录。
3. 处理无效时间差：用ABS()取时间差的绝对值，避免因时间顺序颠倒导致的负时长，再将秒数求和后转换回时间格式。
4. 分组统计：按part_id分组，得到每个部件的累计使用时长。

查询结果 #

执行上述SQL后，会得到你期望的结果：

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