选择可计费时数和不可计费时数的金额。
选择可计费时数和不可计费时数的金额。
提问于 2014-03-24 10:23:56
我是从数据库开始的，所以请对我好一点。我想写的应用程序看起来很基本，我试过了，但是失败了。
我的问题是:我有这个：
表:雇员
# | Colonne | Type | Interclassement | Attributs | Null | Défaut | Extra | Action
1 | Id | int(11) | no | Aucune | AUTO_INCREMENT
2 | Name | varchar(50) | latin1_swedish_ci | yes | NULL
3 | Firstname | varchar(50) | latin1_swedish_ci | yes | NULL |
4 | IdNumber | int(11) | yes | NULL
5 | Mail | varchar(50) | latin1_swedish_ci | no | Aucune |
6 | PassWord | varchar(50) | latin1_swedish_ci | no | Aucune |
7 | Site | varchar(50) | latin1_swedish_ci | yes | NULL |
8 | Entrance | date | yes | NULL |
9 | Departure | date | yes | NULL |
10 | Car_Id | int(11) | yes | NULL |
11 | Profil_Id | int(11) | yes | NULL | 表:估算
# | Colonne | Type | Interclassement | Attributs | Null | Défaut | Extra | Action
1 | Id | int(11) | | | no | Aucune | AUTO_INCREMENT
2 | Hours | int(11) | | | yes | NULL |
3 | Description | varchar(256) | latin1_swedish_ci | | yes | NULL |
4 | ToBeBilled | tinyint(1) | | | yes | 1 |
5 | BillNumber | int(11) | | | yes | NULL |
6 | Day | date | | | yes | NULL |
7 | TimeSheet_Id | int(11) | | | no | Aucune |
8 | Project_Id | int(11) | | | no | Aucune |
9 | automatic | tinyint(1) | | | no | 0 | 表: TimeSheet
# | Colonne | Type | Interclassement | Attributs | Null | Défaut | Extra | Action
1 | Id | int(11) | | | no | Aucune | AUTO_INCREMENT
2 | Month | int(2) | | | yes | NULL |
3 | Year | int(4) | | | yes | NULL |
4 | Filled | tinyint(1) | | | yes | 0 |
5 | Closed | tinyint(1) | | | yes | 0
6 | Employee_Id | int(11) | | | no | Aucune | 我希望取得以下成果：
________________________________________________________
Name | Billable hours | Non-billable hours | Total hours
________________________________________________________
John Doe | 872 | 142 | 1014
________________________________________________________可计费时间是那些带有ToBeBilled line= true的小时数。不可计费的时间是ToBeBilled line= false。
下面是我目前正在处理的SQL查询(我使用FlySpeedSQL查询工具帮助构建我的SQL查询)：
Select
Employee.Name,
Sum( Imputation.Hours),
Imputation.ToBeBilled
From
Employee Inner Join
TimeSheet On TimeSheet.Employee_Id = Employee.Id,
Imputation
Where
Imputation.ToBeBilled = 'true'
Group By
Employee.Name, Imputation.ToBeBilled
Order By
Employee.Name在帮助之后，下面是最后一个查询：
Select
Employee.Name As Name,
Sum(Case When Imputation.ToBeBilled = '1' Then Imputation.Hours End) As `Billable`,
Sum(Case When Imputation.ToBeBilled = '0' Then Imputation.Hours End) As `NonBillable`,
Sum(Imputation.Hours) As `Total`
From
Employee Inner Join
TimeSheet On TimeSheet.Employee_Id = Employee.Id Inner Join
Imputation On Imputation.TimeSheet_Id = TimeSheet.Id
Group By
Employee.Name, Employee.Id
Order By
Name回答 3
Stack Overflow用户
回答已采纳
发布于 2014-03-24 11:10:31
首先，在所有表之间进行适当的连接。只是不要在from语句中使用逗号。然后在case中执行条件聚合--嵌套sum()语句。
Select e.Name, Sum( i.Hours) as TotalHours,
sum(case when i.ToBeBilled = 1 then i.Hours else 0 end) as Billable,
sum(case when i.ToBeBilled = 0 then i.Hours else 0 end) as NonBillable
From Employee e Inner Join
TimeSheet ts
On ts.Employee_Id = e.Id Inner Join
Imputation i
on i.TimeSheetID = ts.TimeSheetId
Group By e.Name, e.id
Order By Employee.Name;这假设ToBeBilled所取的值是0和1。将e.Id包含在group by中是为了处理两个雇员同名的情况。
Stack Overflow用户
发布于 2014-03-24 10:29:56
SELECT
Employee.Name,
Sum(CASE WHEN Imputation.ToBeBilled = 'true' THEN Imputation.Hours END) As Billable_hours,
Sum(CASE WHEN Imputation.ToBeBilled != 'true' THEN Imputation.Hours END) As NonBillable_hours,
SUM(Imputation.Hours) As total_hours
FROM
Employee INNER JOIN TimeSheet
On TimeSheet.Employee_Id = Employee.Id
INNER JOIN Imputation
ON Imputation.TimeSheet_Id=TimeSheet.id
GROUP BY
Employee.Id, Employee.Name
ORDER BY
Employee.NameStack Overflow用户
发布于 2014-03-24 10:47:13
就像这样，
Select Employee.Name,
(Select sum(Imputation.Hours) from TimeSheet where (TimeSheet.Employee_Id = Employee.Id) AND (Imputation.ToBeBilled = 'true')) as BillableHours,
(Select sum(Imputation.Hours) from TimeSheet where (TimeSheet.Employee_Id = Employee.Id) AND (Imputation.ToBeBilled = 'false')) as NonBillableHours,
(Select sum(Imputation.Hours) from TimeSheet where (TimeSheet.Employee_Id = Employee.Id) ) as TotalHours
FROM Employee 页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/22606628
复制相关文章
相似问题
腾讯云开发者
