SQL查询，包括SUM和GROUPBY

Question

我正在创建一个时间跟踪系统，我正在努力处理一个相当复杂的SQL查询。

基本上，我想创建一个特定模块的所有任务的列表，当前有工作会话记录，包括在每个任务上花费的总时间。

到目前为止，我已经使用TIMESTAMPDIFF()来比较StartTime和EndTime列，以计算在TimeSpent的特定工作会话上花费的总时间。然而，我正在努力理解我如何将AssignmentID和SUM()所有TimeSpent列的工作会话分组在一起，以给出总体总数。

这是我到目前为止构造的。

SELECT t2.*, 
SUM( TIMESTAMPDIFF(second, t1.StartTime, t1.EndTime) ) AS TimeSpent 
FROM work_session t1 
LEFT JOIN assignment t2 ON t1.AssignmentID = t2.AssignmentID 
LEFT JOIN module t3 ON t2.ModuleID = t3.ModuleID 
LEFT JOIN course t4 ON t3.CourseID = t4.CourseID 
WHERE t2.ModuleID = 3
GROUP BY t1.AssignmentID   

Answer 1

对于"super aggregate"数据使用WITH ROLLUP:

SELECT t2.*, 
SUM( TIMESTAMPDIFF(second, t1.StartTime, t1.EndTime) ) AS TimeSpent 
FROM work_session t1 
LEFT JOIN assignment t2 ON t1.AssignmentID = t2.AssignmentID 
LEFT JOIN module t3 ON t2.ModuleID = t3.ModuleID 
LEFT JOIN course t4 ON t3.CourseID = t4.CourseID 
WHERE t2.ModuleID = 3
GROUP BY t1.AssignmentID
WITH ROLLUP

From the docs:

mysql> SELECT year, SUM(profit) FROM sales GROUP BY year;
+------+-------------+
| year | SUM(profit) |
+------+-------------+
| 2000 |        4525 |
| 2001 |        3010 |
+------+-------------+

与汇总:

mysql> SELECT year, SUM(profit) FROM sales GROUP BY year WITH ROLLUP;
+------+-------------+
| year | SUM(profit) |
+------+-------------+
| 2000 |        4525 |
| 2001 |        3010 |
| NULL |        7535 |
+------+-------------+