首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >在同一个表中添加不同的列,如mysql中的内部联接

问在同一个表中添加不同的列,如mysql中的内部联接
EN

Stack Overflow用户
提问于 2015-01-23 08:38:40
回答 2查看 110关注 0票数 1

我想报告一下具体项目的时间输入情况。我尝试了下面的查询。

Table1:项目

代码语言:javascript
复制
id   | Name
------------
1    |  A
2    |  B

Table2: EmployeeTimeEntry

代码语言:javascript
复制
proj | activity  |time
----------------------
1    | coding    | 5
2    | coding    | 2
1    | testing   | 2
1    | coding    | 2

我想要的proj输出:

代码语言:javascript
复制
proj  | TotalDur  | activity | Activitytime
--------------------------------------------
A     |  9        | coding   |  7
A     |  9        | testing  |  2

我的问题是:

代码语言:javascript
复制
$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";

但我得到的产出是

代码语言:javascript
复制
proj  | TotalDur  | activity | Activitytime
--------------------------------------------
A     |  9        | coding   |  9
A     |  9        | testing  |  2

添加所有项目的所有活动时间。我使用combobo来选择要显示报告的项目。

EN

回答 2

Stack Overflow用户

回答已采纳

发布于 2015-01-23 09:00:14

我觉得你把事情复杂化了

代码语言:javascript
复制
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

演示

票数 1
EN

Stack Overflow用户

发布于 2015-01-23 08:57:37

也许这就是你想要的?

代码语言:javascript
复制
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.id

示例SQL Fiddle

票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/28105937

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档