将 Connect by、inner join 和 sum 与 Oracle 混合在一起
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2922167/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Mixing together Connect by, inner join and sum with Oracle
提问by Fran?ois
I need help with a oracle query.
我需要有关 oracle 查询的帮助。
Here is my setup:
这是我的设置：
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.
我有 2 个表，分别称为“任务”和“时间表”。“任务”表是一个递归表，这样每个任务可以有多个子任务。每个时间表都与一个任务（不一定是“根”任务）相关联，并包含处理它的小时数。
Example:
例子：
Tasks
任务
id:1 | name: Task A | parent_id: NULL
编号：1 | 名称： 任务 A | parent_id: NULL
id:2 | name: Task A1 | parent_id: 1
编号：2 | 名称：任务A1 | 父 ID：1
id:3 | name: Task A1.1 | parent_id: 2
编号：3 | 名称：任务A1.1 | 父 ID：2
id:4 | name: Task B | parent_id: NULL
编号：4 | 名称： 任务 B | parent_id: NULL
id:5 | name: Task B1 | parent_id: 4
编号：5 | 名称：任务B1 | parent_id：4
Timesheets
时间表
id:1 | task_id: 1 | hours: 1
编号：1 | task_id: 1 | 小时数：1
id:2 | task_id: 2 | hours: 3
编号：2 | task_id: 2 | 小时数：3
id:3 | task_id:3 | hours: 1
编号：3 | task_id:3 | 小时数：1
id:5 | task_id:5 | hours:1 ...
编号：5 | task_id:5 | 小时数：1 ...
What I want to do:
我想做的事：
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:
我想要一个查询，它将返回在“任务层次结构”上工作的所有小时数的总和。如果我们看一下前面的例子，这意味着我想要得到以下结果：
task A - 5 hour(s) | task B - 1 hour(s)
任务 A - 5 小时 | 任务 B - 1 小时
At first I tried this
起初我试过这个
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
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.
它几乎可以工作。唯一的问题是，如果根任务没有时间表，它将跳过整个层次结构……但子行可能有时间表，这正是任务 B1 发生的情况。我知道是“内部连接”部分导致了我的问题，但我不确定如何摆脱它。
Any idea how to solve this problem?
知道如何解决这个问题吗？
Thank you
谢谢
采纳答案by aw crud
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
回答by FrustratedWithFormsDesigner
Have you tried this?
你试过这个吗？
SELECT TaskName, Sum(Hours) "TotalHours"
FROM (
SELECT replace(sys_connect_by_path(decode(level, 1, t.name), '~'), '~') As TaskName,
ts.hours as hours
FROM timesheets ts LEFT OUTER JOIN tasks t 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
回答by Bharat
If you use left outer join instead of normal join, you may get the output.
如果您使用左外连接而不是普通连接，您可能会得到输出。
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,timesheets ts where 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
