table1:
org_father_id org_child_id
2 5
2 6
3 7
3 8
4 9
4 10
table2:
org_id org_name
2 开发部1
3 开发部2
4 开发部3
5 aaa
6 bbb
7 ccc
8 ddd
9 eee
10 fff
得到下面的结果:
id name
2 开发部1
5 开发部1aaa
6 开发部1bbb
3 开发部2
7 开发部2ccc
8 开发部2ddd
4 开发部3
9 开发部3eee
10 开发部3fff
org_father_id org_child_id
2 5
2 6
3 7
3 8
4 9
4 10
table2:
org_id org_name
2 开发部1
3 开发部2
4 开发部3
5 aaa
6 bbb
7 ccc
8 ddd
9 eee
10 fff
得到下面的结果:
id name
2 开发部1
5 开发部1aaa
6 开发部1bbb
3 开发部2
7 开发部2ccc
8 开发部2ddd
4 开发部3
9 开发部3eee
10 开发部3fff
from table2 t1,table1 t2, table2 t3
where t1.id=t2.org_father_id(+) and t2.org_child_id=t3.id(+)
select id,name
from
(select
b.org_id fid,
(case when a.org_child_id is null then a.id else a.org_child_id end) id,
(case when a.org_child_id is null then a.org_name else b.org_name||a.org_name end) name
from
(select case when a.org_father_id is null then b.org_id else a.org_father_id end id,a.org_child_id,b.org_name from table1 a,table2 b where a.org_child_id(+)=b.org_id) a,
(select distinct b.org_id,b.org_name from table1 a,table2 b where a.org_father_id=b.org_id) b
where a.id=b.org_id(+)
)
order by fid,id;
from table2 t1,table1 t2, table2 t3
where t1.id=t2.org_father_id(+) and t2.org_child_id=t3.id(+)
不好意思,我这也有个问题,帮忙解答一下吧!select T_JISSEKI_LF_T_JISSEKI_TOKUSAI.*, T_TSUCHISYO_LOT-T_TSUCHISYO.Priority,T_TSUCHISYO_LOT-T_TSUCHISYO.TSUCHISYO_NO
from
(
select TJISSEKI.NYUKEN_YMD,TJISSEKI.LOT_NO,TJISSEKI.GOUHI_KBN,
TJISSEKI.HANTEI_YMD,TJISSEKI.NONYU_SU,TJISSEKI.SAMPLE_YMD,TJISSEKITOKUSAI.TOKUSAI_NO
from
(
select * from
(
select *
from T_JISSEKI
where HINMEI='*L51399-OJ' and MAKER_CD='HAC3' and KYOTENKANRI_CD='A202' and
(GOUHI_KBN=1 or GOUHI_KBN=2) and DATA_STATUS=3
order by NYUKEN_YMD desc,SORTORDER_NYUKEN desc
)T_JISSEKI where rownum < 51
)TJISSEKI
left join (select NYUKEN_ID , NYUKEN_SEQ , max(substr(sys_connect_by_path( TOKUSAI_NO, ','), 2)) TOKUSAI_NO
from
(
select NYUKEN_ID , NYUKEN_SEQ, TOKUSAI_NO, colc, lead(colc) over(partition by NYUKEN_ID , NYUKEN_SEQ order by colc) cold
from ( select NYUKEN_ID , NYUKEN_SEQ, TOKUSAI_NO , row_number() over(order by NYUKEN_ID , NYUKEN_SEQ) colc
from T_JISSEKI_TOKUSAI )
)
start with cold is null
connect by prior colc=cold
group by NYUKEN_ID , NYUKEN_SEQ
) TJISSEKITOKUSAIon TJISSEKI.NYUKEN_ID=TJISSEKITOKUSAI.NYUKEN_ID
and TJISSEKI. NYUKEN_SEQ=TJISSEKITOKUSAI.NYUKEN_SEQ) T_JISSEKI_LF_T_JISSEKI_TOKUSAI
left join
(
select T_TSUCHISYO_LOT.NYUKEN_ID,T_TSUCHISYO_LOT.NYUKEN_SEQ,
T_TSUCHISYO_LOT.TSUCHISYO_NO,T_TSUCHISYO.TSUCHISYO_NO,
T_TSUCHISYO.Priority
from T_TSUCHISYO_LOT,T_TSUCHISYO
where T_TSUCHISYO_LOT.TSUCHISYO_NO=T_TSUCHISYO.TSUCHISYO_NO) T_TSUCHISYO_LOT-T_TSUCHISYO on T_TSUCHISYO_LOT-T_TSUCHISYO.NYUKEN_ID=T_JISSEKI_LF_T_JISSEKI_TOKUSAI.NYUKEN_ID
and T_TSUCHISYO_LOT-T_TSUCHISYO.NYUKEN_SEQ=JISSEKI_LF_T_JISSEKI_TOKUSAI.NYUKEN_SEQ红色部分都是测试正确的,在进行左连接的时候,报 ora-00918 列未定义 错误????
是什么原因,没搞清楚?????????????
from table2 t1,table1 t2, table2 t3
where t1.id=t2.org_father_id(+) and t2.org_child_id=t3.id(+)
这个sql语句是错的,查询不到符合楼主的条件;talbe2没有id字段,把table2的org_id改为id查询的结果也不符合。
顶一下liangzi_ ,思路好好好噢
from table2 t2
left outer join table1 t1 on t1.org_child_id=t2.org_id
left outer join table2 t3 on t1.org_father_id=t3.org_id