请教一个 SQL

[hudingchen]

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

[bjt_]

select t2.id, t3.name||t1.name
from table2 t1,table1 t2, table2 t3
where t1.id=t2.org_father_id(+) and t2.org_child_id=t3.id(+)

[_liangzi_]

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;

[Croatia]

是不是使用connect by好一些呢？

[Croatia]

同时使用上SYS_CONNECT_BY_PATH函数。

不过，不是什么版本都支持。

[wenchanyee]

select   t2.id,   t3.name ¦ ¦t1.name 
from   table2   t1,table1   t2,   table2   t3 
where   t1.id=t2.org_father_id(+)   and   t2.org_child_id=t3.id(+)

[kinglht]

有这么多回复的，帮你顶一个！

[tmd_love_you]

不好意思，我这也有个问题，帮忙解答一下吧！

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
) TJISSEKITOKUSAI

on 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 列未定义 错误？？？？
是什么原因，没搞清楚？？？？？？？？？？？？？

[sanmmu]

select t2.id, t3.name ¦ ¦t1.name
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_ ，思路好好好噢。。。

[lychttp]

select t2.org_id ,nvl(t3.org_name||t2.org_name,t2.org_name)
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

[lychttp]

对了,忘了排序,LZ有需要自己加个别名和排序就OK了