SQL:结合两个表的日期之间的item的使用时间

Question

试图创建查询，将给我使用该部件的日期之间的每个汽车部件的使用时间。等等，假设零件id 1安装在2018-03-01，在2018-04-01运行50分钟，然后在2018-05-10运行30分钟，该零件的总使用量应该是1:20分钟。

这些是我的表格的例子。

Table1
| id | part_id | car_id | part_date  |
|----|-------- |--------|------------|
| 1  | 1       | 3      | 2018-03-01 |
| 2  | 1       | 1      | 2018-03-28 |
| 3  | 1       | 3      | 2018-05-10 |
Table2
| id | car_id | run_date   | puton_time          | putoff_time         |
|----|--------|------------|---------------------|---------------------|
| 1  | 3      | 2018-04-01 | 2018-04-01 12:00:00 | 2018-04-01 12:50:00 |
| 2  | 2      | 2018-04-10 | 2018-04-10 15:10:00 | 2018-04-10 15:20:00 |
| 3  | 3      | 2018-05-10 | 2018-05-10 10:00:00 | 2018-05-10 10:30:00 |
| 4  | 1      | 2018-05-11 | 2018-05-11 12:00:00 | 2018-04-01 12:50:00 |

表1包含日期时，每个部分的安装，表2包含每个部分的使用时间，他们在car_id上加入，我试着写查询，但它不能很好地工作，如果有人能找出我的错误在这个查询，这将是有益的。

我的SQL查询

SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(t1.puton_time, t1.putoff_time)))) AS total_time
FROM table2 t1 
LEFT JOIN table1 t2 ON t1.car_id=t2.car_id
WHERE t2.id=1 AND t1.run_date BETWEEN t2.datum AND 
(SELECT COALESCE(MIN(datum), '2100-01-01') AS NextDate FROM table1 WHERE 
id=1 AND t2.part_date > part_date);
Expected result
| part_id | total_time |
|---------|------------|
| 1       | 1:20:00    |   

希望这个问题是有意义的，因为在我的搜索中我没有发现这样的，所以我需要帮助。

解决方案，感谢Kota Mori

SELECT t1.id, SEC_TO_TIME(SUM(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 t1.part_date >= t2.run_date
GROUP BY t1.id

Answer 1

首先需要通过car_id和part_date不大于run_date的条件连接两个表。然后分别计算每个part_id的总分钟数

以下是SQLite(我现在唯一可以访问的SQL引擎)的查询示例。由于SQLite没有日期时间类型，我通过strftime函数将字符串转换为unix时间戳。这部分应该根据您使用的SQL引擎进行更改。除此之外，这是一个相当标准的sql，并且对其他sql方言大多有效。

SELECT
t1.id,
sum(
cast(strftime('%s', t2.putoff_time) as integer) -
cast(strftime('%s', t2.puton_time) as integer)
) / 60 AS total_minutes
FROM
table1 t1
LEFT JOIN
table2 t2
ON
t1.car_id = t2.car_id
AND t1.part_date <= t2.run_date
GROUP BY
t1.id

结果如下所示。注意，ID 1得到了预期的80分钟(1:20)。

id  total_minutes
0   1             80
1   2             80
2   3             30