SQL查询 - 从层次结构的两个不同水平
问题描述:
一个项目具有多个任务,其具有多个分配SQL查询 - 从层次结构的两个不同水平
获取和的项目(1-N) - >任务(1-N) - >分配
甲任务表上的字段是计划小时数。
Assignments表上的字段是Assigned Hours。
如何在单个查询中获得所有项目的计划小时数和分配小时数?
答
select p.ProjectID, t.PlannedHoursSum, a.AssignedHoursSum
from Projects p
inner join (
select ProjectID, sum(PlannedHours) as PlannedHoursSum
from Tasks
group by ProjectID
) t on p.ProjectID = t.ProjectID
inner join (
select t.ProjectID, sum(AssignedHours) as AssignedHoursSum
from Tasks t
inner join Assignments a on t.TaskID = a.TaskID
group by t.ProjectID
) a on p.ProjectID = a.ProjectID
答
编辑:这个答案给出了不正确的结果,但我将它留作历史用途。
我认为这会帮助你。但我无法弄清楚如何摆脱Distinct
。
Select Distinct
Project.ProjectId,
Sum(Task.PlannedHours)
Over (Partition By Project.ProjectId) As PlannedHours,
Sum(Assignment.AssignedHours)
Over (Partition By Project.ProjectId) As AssignedHours
From Project
Inner Join Task On Project.ProjectId = Task.ProjectId
Inner Join Assignment On Task.TaskId= Assignment.AssignmentId
这是我的测试脚本。
Declare @Project Table (ProjectId Int)
Declare @Task Table (TaskId Int, ProjectId Int, PlannedHours Int)
Declare @Assignment Table (AssignmentId Int, TaskId Int, AssignedHours Int)
Insert Into @Project (ProjectId) values (1)
Insert Into @Project (ProjectId) values (2)
Insert Into @Project (ProjectId) values (3)
Insert Into @Task (TaskId, ProjectId, PlannedHours) values (1, 1, 10)
Insert Into @Task (TaskId, ProjectId, PlannedHours) values (2, 1, 10)
Insert Into @Task (TaskId, ProjectId, PlannedHours) values (3, 2, 11)
Insert Into @Task (TaskId, ProjectId, PlannedHours) values (4, 3, 12)
Insert Into @Assignment (AssignmentId, TaskId, AssignedHours) values (1, 1, 2)
Insert Into @Assignment (AssignmentId, TaskId, AssignedHours) values (2, 1, 2)
Insert Into @Assignment (AssignmentId, TaskId, AssignedHours) values (3, 2, 2)
Insert Into @Assignment (AssignmentId, TaskId, AssignedHours) values (4, 3, 3)
Select Distinct
Project.ProjectId,
Sum(Task.PlannedHours)
Over (Partition By Project.ProjectId) As PlannedHours,
Sum(Assignment.AssignedHours)
Over (Partition By Project.ProjectId) As AssignedHours
From @Project Project
Inner Join @Task Task On Project.ProjectId = Task.ProjectId
Inner Join @Assignment Assignment On Task.TaskId= Assignment.AssignmentId
+1
+1使用'求和Partition',而不是子查询和临时表。 – 2010-04-06 23:19:38
这非常接近我要去的地方。谢谢! – 2010-04-06 22:19:52
@Matthew - 我真的不在乎代表,但如果我维护你的应用程序,我宁愿在我的答案中看到更简洁的版本。我还比较了这两个答案的查询计划,估计这个问题需要我的答案的两倍。很明显,只有在您尝试使用大型数据集时才会知道,但需要考虑。 – ChaosPandion 2010-04-06 22:25:15
@Matthew琼斯,我同意@ChaosPandion在这一个。 'Sum Over Partition'比@OrbMan使用的子查询和临时表更方便。 – 2010-04-06 23:18:40