MySQL查询。带条件提取 Sum(total) 和 Sum(total)

Question

关于我的问题SQL是，

我有一个表，用户可以在其中输入名为 time_report 的时间报告数据。每个 time_report 都输入一个服务代码，每个代码都有不同的含义，包括开始、停止、时间和总计。

这是在 time_report 表中输入的数据示例。

t_id Date User Customer Service Closing System  Part    Start   Stop    Total53 2011-05-02 13 0   48 4   0   0   09:00   17.15   8.1554 2011-05-03 13 0   49 4   0   0   09:00   17:00    855 2011-05-04 13 0   48 4   0   0   09:00   17.15   8:1561 2011-05-04 1  0  52  4   0   0   09:00   17.15   8:1562 2011-05-05 1  0  48  4   0   0   09:00   17.15   8:15

我正在尝试运行查询以提取 3 条信息。

user ( the user is the foreign key to users.user_id)sum(total) per user.sun(total) per user where service < 49 Utilization Percentage: (This will be calculated by dividing the 2 totals )

服务是servicecodes.s_id的外键

当我尝试运行查询以获取总和时(总计工作正常，但我无法将信息组合在一起

mysql> SELECT  Users.full_name,sum(total)    -> FROM time_report, users    -> WHERE time_report.User = users.user_id    -> AND date    -> BETWEEN '2011-0502'    -> AND '2011-05-11'    -> GROUP BY User;

+-----------------+------------+| full_name       | sum(total) |+-----------------+------------+| Cian Higgins    |         26 || Wallace Ward    |         23 || jason ward      |         42 || Thomas Woods    |         72 || Peter Jones     |         49 || fintan corrigan |         40 || David Jones     |         35 || January Jones   |         23 || Joe Johnson     |         24 |+-----------------+------------+9 rows in set, 1 warning (0.09)

当我使用服务 < 49

运行查询时

SELECT  Users.full_name,sum(total) AS ProductiveFROM time_report, usersWHERE time_report.User = users.user_idAND dateBETWEEN '2011-0502'AND '2011-05-11'AND Service < 49GROUP BY User;+-----------------+------------+| full_name       | Productive |+-----------------+------------+| Cian Higgins    |         14 || Wallace Ward    |         23 || jason ward      |         33 || Thomas Woods    |         53 || Peter Jones     |         41 || fintan corrigan |         32 || David Jones     |         27 || January Jones   |         23 || Joe Johnson     |         24 |+-----------------+------------+9 rows in set, 1 warning (0.03 sec)

但是如果我尝试加入报告，我会收到错误....如果我尝试将它作为子查询运行，我也会收到错误

例子:

SELECT Users.full_name, sum( total ) FROM time_report, usersWHERE time_report.User = users.user_idAND dateBETWEEN '2011-0502'AND '2011-05-11'GROUP BY UserUNION SELECT Users.full_name, sum( total ) AS ProductiveFROM time_report, usersWHERE time_report.User = users.user_idAND dateBETWEEN '2011-0502'AND '2011-05-11'AND Service <49GROUP BY User 

这会在一行中给出所有结果

full_name sum(total) Cian Higgins 26 Wallace Ward 23 jason ward 42 Thomas Woods 72 Peter Jones 49 fintan corrigan 40 David Jones 35 January Jones 23 Joe Johnson 24 Cian Higgins 14 jason ward 33 Thomas Woods 53 Peter Jones 41 fintan corrigan 32 David Jones 27 

Answer 1

你可以使用 CASE语句来优化总和，并用一个语句返回两个总和。

SELECT  Users.full_name
        ,sum(total)
        ,sum(case when service < 49 then total else 0 end) AS Productive
FROM    time_report
        , users
WHERE   time_report.User = users.user_id
        AND date BETWEEN '2011-0502' AND '2011-05-11'
GROUP BY 
        User