mysql - 如何将 where 子句添加到总和中？

Question

我正在尝试找出最佳方法来获取一个人在遵循特定模式的项目名称上花费的总时间

当前表

+--------------------+----------------+-----------------+
|    Tbl_Employee    |  Tbl Projects  |  tbl_timesheet  |
+--------------------+----------------+-----------------+
| employee_id        | project_id     | timesheet_id    |
| employee_full_name | cws_project_id | employee_id     |
|                    |                | project_id      |
|                    |                | timesheet_hours |
+--------------------+----------------+-----------------+

这是我到目前为止的查询

select
te.employee_id, 
te.employee_last_name,
te.employee_first_name,
te.employee_department,
te.employee_type_id,
te.timesheet_routing,
sum(tt.timesheet_hours) as total_hours,
month(tt.timesheet_date) as "month",
year(tt.timesheet_date) as "year"

from tbl_employee te
left join tbl_timesheet tt
on te.employee_id = tt.employee_id
join tbl_projects tp 
on tp.project_id = tt.project_id
where te.employee_active = 1
and te.employee_id > 0
and employee_department IN ("Project Management","Engineering","Deployment Srvs.")
and year(tt.timesheet_date) = 2015
group by te.employee_last_name, year(tt.timesheet_date), month(tt.timesheet_date)
order by employee_last_name

我需要添加到我的选择语句中的是一些效果

sum(tt.timesheet_hours) as where cws_project_id like '%Training%' as training

简而言之，我需要知道员工为 cws_project_id 包含单词培训的项目贡献的总小时数。我知道你不能在 Sum 中添加一个 where 子句，但我似乎无法找到另一种方法来做到这一点。

如果这会产生影响，我需要多次执行此操作 - 即，project_name 包含不同的单词。

非常感谢您提供的任何帮助。我希望这不会像泥一样清澈。

Answer 1

以下是您要查找的内容的一般形式:

SELECT SUM(IF(x LIKE '%y%', z, 0)) AS ySum

更通用

SELECT SUM(IF([行条件], [行中的值或计算], 0)) AS [partialSum]

<小时/>

编辑:为了获得更多 RDBMS 可移植性(早期版本的 MS SQL 不支持这种形式的 IF):

SELECT SUM(CASE WHEN [行条件] THEN [行中的值或计算] ELSE 0 END) AS [partialSum]