mysql - SQL 时差和按 3 列分组

Question

我有一个 MySQL 表，我想在其中计算每个人花费的总时间，即按 3 列分组的时间差。该表如下所示：

我想要这样的输出：

我可以按 per_1 分组，但不能全部分组。

SELECT per_1, TIME_TO_SEC(TIMEDIFF(end_time,start_time)) / 3600 AS Duration 
FROM os 
GROUP BY per_1

任何建议将不胜感激。

标签： mysql

Answer 1

架构对我来说看起来不正确。不过，您可以通过以下方式获得相同的结果UNION ALL：

SELECT Person, SUM(Duration)
FROM (
         SELECT per_1 AS Person, TIME_TO_SEC(TIMEDIFF(end_time, start_time)) / 3600 AS Duration
         FROM os
         WHERE per_1 IS NOT NULL AND per_1 <> ''

         UNION ALL

         SELECT per_2 AS Person, TIME_TO_SEC(TIMEDIFF(end_time, start_time)) / 3600 AS Duration
         FROM os
         WHERE per_2 IS NOT NULL AND per_2 <> ''

         UNION ALL

         SELECT per_3 AS Person, TIME_TO_SEC(TIMEDIFF(end_time, start_time)) / 3600 AS Duration
         FROM os
         WHERE per_3 IS NOT NULL AND per_3 <> ''
     ) AS derived
GROUP BY Person
ORDER BY SUM(Duration) DESC;