混合在一起通过Oracle内部连接和求和 [英] Mixing together Connect by, inner join and sum with Oracle
问题描述
我需要有关oracle查询的帮助.
I need help with a oracle query.
这是我的设置:
我有2个表,分别称为任务"和时间表". 任务"表是一个递归表,这样每个任务可以具有多个子任务.每个时间表都与一个任务(不一定是根"任务)相关联,并包含处理该任务的小时数.
I have 2 tables called respectively "tasks" and "timesheets". The "tasks" table is a recursive one, that way each task can have multiple subtasks. Each timesheet is associated with a task (not necessarily the "root" task) and contains the number of hours worked on it.
示例:
id:1 |名称:任务A | parent_id:NULL
id:1 | name: Task A | parent_id: NULL
id:2 |名称:任务A1 | parent_id:1
id:2 | name: Task A1 | parent_id: 1
id:3 |名称:任务A1.1 | parent_id:2
id:3 | name: Task A1.1 | parent_id: 2
id:4 |名称:任务B | parent_id:NULL
id:4 | name: Task B | parent_id: NULL
id:5 |名称:任务B1 | parent_id:4
id:5 | name: Task B1 | parent_id: 4
id:1 | task_id:1 |小时:1
id:1 | task_id: 1 | hours: 1
id:2 | task_id:2 |小时:3
id:2 | task_id: 2 | hours: 3
id:3 | task_id:3 |小时:1
id:3 | task_id:3 | hours: 1
id:5 | task_id:5 |小时:1 ...
id:5 | task_id:5 | hours:1 ...
我想要一个查询,该查询将返回任务层次结构"上所有工作时间的总和.如果我们看一下前面的示例,这意味着我希望获得以下结果:
I want a query that will return the sum of all the hours worked on a "task hierarchy". If we take a look at the previous example, It means I would like to have the following results:
任务A-5小时|任务B-1小时(s)
task A - 5 hour(s) | task B - 1 hour(s)
SELECT TaskName, Sum(Hours) "TotalHours"
FROM (
SELECT replace(sys_connect_by_path(decode(level, 1, t.name), '~'), '~') As TaskName,
ts.hours as hours
FROM tasks t INNER JOIN timesheets ts ON t.id=ts.task_id
START WITH PARENTOID=-1
CONNECT BY PRIOR t.id = t.parent_id
)
GROUP BY TaskName Having Sum(Hours) > 0 ORDER BY TaskName
几乎可以正常工作.唯一的问题是,如果没有针对根任务的时间表,它将跳过整个层次结构...但是可能会有针对子行的时间表,而这正是任务B1发生的事情.我知道是导致问题的原因是内部联接"部分,但是我不确定如何摆脱它.
And it almost work. THe only problem is that if there are no timesheet for a root task, it will skip the whole hieararchy... but there might be timesheets for the child rows and it is exactly what happens with Task B1. I know it is the "inner join" part that is causing my problem but I'm not sure how can I get rid of it.
有什么办法解决这个问题吗?
Any idea how to solve this problem?
谢谢
推荐答案
这样的作品有用吗?我遇到过与您类似的情况,我只是从分层查询中删除了联接,然后才应用它,以免丢失行.
Would something like this work? I've had cases similar to yours, and I simply removed the join from the hierarchical query and applied it only afterward to avoid losing rows.
SELECT TaskName, Sum(ts.hours) "TotalHours"
FROM (
SELECT replace(sys_connect_by_path(decode(level, 1, t.name), '~'), '~') As TaskName, t.id
FROM tasks t
START WITH PARENTOID=-1
CONNECT BY PRIOR t.id = t.parent_id
) tasks
INNER JOIN timesheets ts ON tasks.id=ts.task_id
GROUP BY TaskName Having Sum(ts.hours) > 0 ORDER BY TaskName
这篇关于混合在一起通过Oracle内部连接和求和的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!