代码之家 › 专栏 › 技术社区 › François

与Oracle混合使用connect by、inner join和sum

connect-by oracle

François · 技术社区 · 16 年前

我需要Oracle查询的帮助。

以下是我的设置:

我有两个表,分别称为“任务”和“时间表”。“tasks”表是递归的,这样每个任务都可以有多个子任务。每个时间表都与一个任务(不一定是“根”任务)相关联,并包含对其工作的小时数。

例子:

任务

ID:1名称:任务A父项ID:空

ID:2名称:任务A1父项ID:1

ID:3名称:任务A1.1父项ID:2

id:4 name:task b parent_id:null

ID:5名称:任务B1父项ID:4

时间表

ID:1任务ID:1小时:1

ID:2任务ID:2小时:3

ID:3任务ID:3小时:1

ID:5任务ID:5小时:1 …

我想做的是:

我想要一个返回“任务层次结构”上所有工作时间总和的查询。如果我们看一下前面的示例,这意味着我希望得到以下结果:

任务A-5小时任务B-1小时

起初我试过这个

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所发生的事情。我知道是“内部连接”部分导致了我的问题,但我不知道如何才能摆脱它。

知道怎么解决这个问题吗?

谢谢你

3 回复 | 直到 10 年前

aw crud 16 年前

想要这个工作吗?我有类似于您的案例,我只是从层次查询中删除了连接,然后才应用它,以避免丢失行。

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

FrustratedWithFormsDesigner 16 年前

你试过这个吗?

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

Bharat 16 年前

如果使用左外部联接而不是普通联接,则可以获得输出。

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