代码之家 › 专栏 › 技术社区 › Matthew Jones

sql查询-从两个不同层次结构中获取总和

tsql sql-server sql

Matthew Jones · 技术社区 · 15 年前

一个项目有多个任务,其中有多个工作分配

项目(1-n)->任务(1-n)->分配

tasks表中的一个字段是planned hours。

工作分配表上的一个字段被分配工时。

如何在单个查询中获取所有项目的计划工时和分配工时?

2 回复 | 直到 15 年前

D'Arcy Rittich 15 年前

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

ChaosPandion 15 年前

编辑:这个答案给出了不正确的结果,但我会把它留到历史上。

我想这能帮到你。我不知道怎么摆脱 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