17,140
社区成员




with t1 as(
select '111' as id,'0' as fatherid,'湖南' as name from dual union all
select '222' as id,'111' as fatherid,'长沙' as name from dual union all
select '333' as id,'222' as fatherid,'天心区' as name from dual union all
select '444' as id,'222' as fatherid,'XX区' as name from dual union all
select '555' as id,'0' as fatherid,'河北' as name from dual union all
select '666' as id,'555' as fatherid,'石家庄' as name from dual union all
select '777' as id,'555' as fatherid,'邢台' as name from dual union all
select '888' as id,'666' as fatherid,'桥东区' as name from dual
),
t2 as (
select a.*,level lv
from t1 a
start with a.fatherid=0
connect by prior a.id = a.fatherid
)
select a.id province_id,a.name province_name,b.id city_id,b.name city_name,c.id county_id,c.name county_name
from (select * from t2 where lv=1) a left join
(select * from t2 where lv=2 ) b on b.fatherid=a.id
left join (select * from t2 where lv=3) c on c.fatherid=b.id
仅供参考[/quote]
大牛 你这个写的太复杂了啊 我感觉只要
select * from test1 T1
join test1 T2 on T1.father_id = T2.id
join test1 T3 on T2.father_id = T3.id
这样就可以了吧 为啥 还弄 左关联 而且 还有c.id is not null 呢 with t1 as(
select '111' as id,'0' as fatherid,'湖南' as name from dual union all
select '222' as id,'111' as fatherid,'长沙' as name from dual union all
select '333' as id,'222' as fatherid,'天心区' as name from dual union all
select '444' as id,'222' as fatherid,'XX区' as name from dual union all
select '555' as id,'0' as fatherid,'河北' as name from dual union all
select '666' as id,'555' as fatherid,'石家庄' as name from dual union all
select '777' as id,'555' as fatherid,'邢台' as name from dual union all
select '888' as id,'666' as fatherid,'桥东区' as name from dual
),
t2 as (
select a.*,level lv
from t1 a
start with a.fatherid=0
connect by prior a.id = a.fatherid
)
select a.id province_id,a.name province_name,b.id city_id,b.name city_name,c.id county_id,c.name county_name
from (select * from t2 where lv=1) a left join
(select * from t2 where lv=2 ) b on b.fatherid=a.id
left join (select * from t2 where lv=3) c on c.fatherid=b.id
仅供参考[/quote]
大牛 不能用 connect by
-- 借楼上数据
with t1 as(
select '111' as id,'0' as fatherid,'湖南' as name from dual union all
select '222' as id,'111' as fatherid,'长沙' as name from dual union all
select '333' as id,'222' as fatherid,'天心区' as name from dual union all
select '444' as id,'222' as fatherid,'XX区' as name from dual union all
select '555' as id,'0' as fatherid,'河北' as name from dual union all
select '666' as id,'555' as fatherid,'石家庄' as name from dual union all
select '777' as id,'555' as fatherid,'邢台' as name from dual union all
select '888' as id,'666' as fatherid,'桥东区' as name from dual
)
select a.id province_id,a.name province_name,b.id city_id,b.name city_name,c.id county_id,c.name county_name
from t1 a
left join t1 b on a.fatherid=b.id
left join t1 c on b.fatherid=c.id
where c.id is not null
with t1 as(
select '111' as id,'0' as fatherid,'湖南' as name from dual union all
select '222' as id,'111' as fatherid,'长沙' as name from dual union all
select '333' as id,'222' as fatherid,'天心区' as name from dual union all
select '444' as id,'222' as fatherid,'XX区' as name from dual union all
select '555' as id,'0' as fatherid,'河北' as name from dual union all
select '666' as id,'555' as fatherid,'石家庄' as name from dual union all
select '777' as id,'555' as fatherid,'邢台' as name from dual union all
select '888' as id,'666' as fatherid,'桥东区' as name from dual
),
t2 as (
select a.*,level lv
from t1 a
start with a.fatherid=0
connect by prior a.id = a.fatherid
)
select a.id province_id,a.name province_name,b.id city_id,b.name city_name,c.id county_id,c.name county_name
from (select * from t2 where lv=1) a left join
(select * from t2 where lv=2 ) b on b.fatherid=a.id
left join (select * from t2 where lv=3) c on c.fatherid=b.id
仅供参考