我想报告一下具体项目的时间输入情况。我尝试了下面的查询。
Table1:项目
id | Name
------------
1 | A
2 | BTable2: EmployeeTimeEntry
proj | activity |time
----------------------
1 | coding | 5
2 | coding | 2
1 | testing | 2
1 | coding | 2我想要的proj输出:
proj | TotalDur | activity | Activitytime
--------------------------------------------
A | 9 | coding | 7
A | 9 | testing | 2我的问题是:
$query = "SELECT
name as 'Proj',
TimeEntry.Total as 'TotalDur',
ATimeEntry.ADetails as 'activity',
ATimeEntry.ATotal as 'Activitytime'
FROM Projects pr
INNER JOIN(SELECT project,SUM(time) as Total from EmployeeTimeEntry group by project ) TimeEntry on pr.id = TimeEntry.project
INNER JOIN(SELECT project,details as ADetails,SUM(time) as ATotal from EmployeeTimeEntry where id = pr.id group by details ) ATimeEntry on pr.id = TimeEntry.project";但我得到的产出是
proj | TotalDur | activity | Activitytime
--------------------------------------------
A | 9 | coding | 9
A | 9 | testing | 2添加所有项目的所有活动时间。我使用combobo来选择要显示报告的项目。
发布于 2015-01-23 09:00:14
我觉得你把事情复杂化了
select
p.name as Proj,
x.TotalDur,
et.activity,
sum(et.time) as Activitytime
from Projects p
join (
select proj, sum(time) as TotalDur from EmployeeTimeEntry group by proj
)x on x.proj = p.id
join EmployeeTimeEntry et on et.proj = p.id
where p.name = 'A'
group by p.name,et.activity发布于 2015-01-23 08:57:37
也许这就是你想要的?
select
p.Name as Proj,
(select sum(time) as TotalDur from EmployeeTimeEntry where proj = p.id group by proj) TotalDur,
activity,
sum(e.time) as ActivityTime
from Projects p
inner join EmployeeTimeEntry e on e.proj = p.id
where p.Name = 'A'
group by name, activity, p.idhttps://stackoverflow.com/questions/28105937
复制相似问题