也不知道是不是你想要的,你最后能够通过简单的数据来描述你想要的结果:with WBS AS
(select 1 wbsid, null parentid
from dual
union all
select 2 wbsid, 1 parentid
from dual
union all
select 3 wbsid, 2 parentid
from dual
union all
select 4 wbsid, 3 parentid
from dual
union all
select 5 wbsid, 4 parentid from dual),
task as
(select 10 taskid, 1 wbsid
from dual
union all
select 20 taskid, 2 wbsid
from dual
union all
select 30 taskid, 3 wbsid
from dual
union all
select 40 taskid, 4 wbsid
from dual
union all
select 50 taskid, 5 wbsid from dual)
select *
from task,
(select *
from wbs
start with wbsid = 3
connect by parentid = prior wbsid) t1
where task.wbsid = t1.wbsid;
(select 1 wbsid, null parentid
from dual
union all
select 2 wbsid, 1 parentid
from dual
union all
select 3 wbsid, 2 parentid
from dual
union all
select 4 wbsid, 3 parentid
from dual
union all
select 5 wbsid, 4 parentid from dual),
task as
(select 10 taskid, 1 wbsid
from dual
union all
select 20 taskid, 2 wbsid
from dual
union all
select 30 taskid, 3 wbsid
from dual
union all
select 40 taskid, 4 wbsid
from dual
union all
select 50 taskid, 5 wbsid from dual)
select *
from task,
(select *
from wbs
start with wbsid = 3
connect by parentid = prior wbsid) t1
where task.wbsid = t1.wbsid;
(select 1 wbsid, null parentid
from dual
union all
select 2 wbsid, 1 parentid
from dual
union all
select 3 wbsid, 2 parentid
from dual
union all
select 4 wbsid, 3 parentid
from dual
union all
select 5 wbsid, 4 parentid from dual),
task as
(select 10 taskid, 1 wbsid
from dual
union all
select 20 taskid, 2 wbsid
from dual
union all
select 30 taskid, 3 wbsid
from dual
union all
select 40 taskid, 4 wbsid
from dual
union all
select 50 taskid, 5 wbsid from dual)
select *
from task,
(select *
from wbs
start with wbsid = 3
connect by parentid = prior wbsid) t1
where task.wbsid = t1.wbsid;没看懂你写的,写死了么,,不知道WBS有多少层啊。。
FROM (SELECT WBSID
FROM WBS
START WITH WBSID = 你要查询的WBSID
CONNECT BY PARENTID = PRIOR WBSID) WBS,
TASK
WHERE WBS.WBSID = TASK.WBSID(+)
wbs数据:task数据:
select c.parentid, max(c.task_date)
from (select b.taskid, a.wbsid, a.parentid, b.task_date
from (select wbsid, parentid from wbs where parentid = 1) a,
task b
where a.wbsid = b.wbsid) c
group by c.parentid;
运行结果: