一个sql的问题

现在有三个表

EMPLOYEE (EmpID, Name, Email)

 

PROJECT(ProjectID, ProjectName, Description, StartDate, EndDate)

 

ASSIGNMENT(EmpID, ProjectID, TotalHoursWorked)

 

现在有这么个要求,请教一下个人,这个sql怎么写?

 

将名字为“Jones Smith”的员工所参加的所有项目的名称（ProjectName）和这些项目所花费的所有项目参加者的时间总和输出。

评论

2 楼 jerryhanwei 2010-11-18  

select p.projectname,t.totalTime
from
(select a.projectid proID,sum(a.totalhoursworked) totalTime
from assignment a
group by a.projectid having a.projectid in
(select a.projectid from emp e,assignment a
where e.ename like '%SMITH%'
and e.empno = a.empid(+)
)) t,project p
where t.proID = p.projectid

1 楼 yunmanfan 2010-10-25  

有个思路,大家看看
select emp.Name,asitotal.total
  from PROJECT pro,
       ASSIGNMENT asi,
       EMPLOYEE emp,
       (
       select ProjectID,sum(TotalHoursWorked) as total
       from ASSIGNMENT asi
       group by ProjectID
       ) asitotal
  where emp.Name = 'Jones Smith'
    and emp.EmpID = asi.EmpID
    and asi.ProjectID = pro.ProjectID
    and asi.ProjectID = asitotal.ProjectID