MySQL复杂选择与时间聚合

Question

SQL-Fiddle 中的架构:http://sqlfiddle.com/#!2/359c8/1

我试图做的是让所有人在这样的项目上花费的时间(hh:mm):

Date       | Project     | Persons                        | Total Time
------------------------------------------------------------------------
25.11.2013 | Project One | Jane Doe hh:mm, John Doe hh:mm | Total hh:mm
26.11.2013 | Project One | Jane Doe hh:mm, John Doe hh:mm | Total hh:mm
27.11.2013 | Project One | Jane Doe hh:mm, John Doe hh:mm | Total hh:mm
28.11.2013 | Project Four| Jane Doe hh:mm, John Doe hh:mm | Total hh:mm 

我真的不知道如何获得这个输出，尤其是逗号分隔的部分。

希望有人遇到或有类似的问题来帮助我找到解决方案。

Answer 1

SQLFiddle here

根据您的数据 - 我认为您需要首先总结每个人每天每个项目的工作量。 (有一个例子，约翰·多伊(John Doe)一天工作两次)。该解决方案将是(工作单位列只是为了了解一个人每个项目每天是否有多个记录):

SELECT `date`, projectstart, person, COUNT(*) as workunits
      , SUM(TIMEDIFF(CAST(end AS time), CAST(begin AS time))) AS worked
FROM IMPORT
GROUP BY `date`, projectstart, person;

然后为了获得最终结果，您需要总结每天每个项目的结果

SELECT `date`, projectstart
     , GROUP_CONCAT(CONCAT(person,' ',CAST(worked AS time))) AS people
     , SUM(worked) AS worked
FROM (SELECT `date`, projectstart, person, COUNT(*) as workunits
             , SUM(TIMEDIFF(CAST(end AS time), CAST(begin AS time))) AS worked
      FROM IMPORT
      GROUP BY `date`, projectstart, person) AS t
GROUP BY `date`, projectstart;