mysql - 显示参与项目的所有用户并汇总小时数

Question

我有一个如下所示的表格:

+------+--------+---------+
| User | Actual | Project |
+------+--------+---------+
| A    | 5      | ProjA   |
| A    | 8      | ProjA   |
| B    | 3      | ProjA   |
| C    | 2      | ProjA   |
| B    | 8      | ProjB   |
| B    | 8      | ProjB   |
+------+--------+---------+

我的查询是这样的

 SELECT DISTINCT User, 
 SUM(Actual) AS Actual
 FROM Table
 Where Project = "ProjA" AND
 User = "A"

结果是

+------+--------+
| User | Actual |
+------+--------+
| A    | 13     |
+------+--------+

我想要实现的是查询显示参与该项目的所有用户，然后显示每个员工的实际总和，然后如果可能的话获得所有实际的总和，如下所示:

+------+--------+
| User | Actual |
+------+--------+
| A    | 13     |
| B    | 3      |
| C    | 2      |
+------+--------+

+-------+----+
| Total | 18 |
+-------+----+

我应该怎么做？我尝试删除 where 子句中的 User = "A" 但这确实没有帮助。

Answer 1

SELECT user,
     SUM(actual) as actual
FROM `table`
Where Project = "ProjA"
GROUP BY user
WITH ROLLUP

WITH ROLLUP 将为您提供用户值为 NULL 的总行。

sqlFiddle

如果你想返回“Total”而不是 NULL，你可以使用这个

SELECT if(User is NULL,'Total',User) as User,
     SUM(Actual) as Actual
FROM `table`
Where Project = "ProjA"
GROUP BY user
WITH ROLLUP

sqlFiddle

WITH ROLLUP 非常贴心，因为它为所有 GROUP BY 提供了汇总行，因此如果您想删除 WHERE Project = "ProjA" 并进行汇总对于每个项目和所有项目，您可以执行如下操作。

SELECT IF(Project IS NULL,'All Projects',Project) as Project,
       IF(User IS NULL,'Total',User) as User,
     SUM(Actual) as Actual
FROM `table`
GROUP BY Project,User
WITH ROLLUP

sqlFiddle

已更新

如果你只想要 ProjA 的总数，你可以这样做

SELECT 'Total' as User,SUM(Actual) as Actual
FROM `table`
WHERE Project = "ProjA"

或者，如果您只想从 ROLLUP 中选择总行，您可以执行此操作(不确定为什么要这样做，但就是这样)

SELECT User,Actual FROM
(SELECT if(User is NULL,'Total',User) as User,
     SUM(Actual) as Actual
FROM `table`
Where Project = 'ProjA'
GROUP BY User
WITH ROLLUP
 )T
WHERE User = 'Total'

sqlFiddle