一张表通过自连接 通过子ID 和父ID 写出省 市 县的sql

Question

有一张区域分类表test1(id int ,father_id int, name string). 其中father_id 是id的父编号，当father_id为0时表示省份，
现需要实现test2(province_id,province_name,city_id,city_name,county_id,county_name),
要求数据结果字段：province_id,province_name,city_id,city_name,county_id,county_name
请教一下各位大牛如何实现呢

Answer 1

ather_id为0时表示省份，怎么表示市和县

Answer 2

市和县都有具体的数字啊  比如是市的时候    市的父ID 那个列 就存上一个级别的的省的ID 阿     
如果是县的的时候   县的父ID 那一个列  就存的上一个级别的 市的ID阿
如果是省  因为省上一级别没上个级别了  索引存的0 阿 比如   湖南的ID 是111   长沙的ID 是 222   天心区的ID 是333  
那么数据库里的记录就是这样的
id                    fatherid        name
111                 0                   湖南
222                111               长沙
333                 222              天心区

Answer 3

select 
from table
connect  by  prior id = fatherid市和县都有具体的数字啊  比如是市的时候    市的父ID 那个列 就存上一个级别的的省的ID 阿     
如果是县的的时候   县的父ID 那一个列  就存的上一个级别的 市的ID阿
如果是省  因为省上一级别没上个级别了  索引存的0 阿 比如   湖南的ID 是111   长沙的ID 是 222   天心区的ID 是333  
那么数据库里的记录就是这样的
id                    fatherid        name
111                 0                   湖南
222                111               长沙
333                 222              天心区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 仅供参考

Answer 4

-- 借楼上数据 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 

Answer 5

市和县都有具体的数字啊  比如是市的时候    市的父ID 那个列 就存上一个级别的的省的ID 阿     
如果是县的的时候   县的父ID 那一个列  就存的上一个级别的 市的ID阿
如果是省  因为省上一级别没上个级别了  索引存的0 阿 比如   湖南的ID 是111   长沙的ID 是 222   天心区的ID 是333  
那么数据库里的记录就是这样的
id                    fatherid        name
111                 0                   湖南
222                111               长沙
333                 222              天心区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 仅供参考
大牛 不能用 connect  by  

Answer 6

版主   我感觉  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 呢

Answer 7

市和县都有具体的数字啊  比如是市的时候    市的父ID 那个列 就存上一个级别的的省的ID 阿     
如果是县的的时候   县的父ID 那一个列  就存的上一个级别的 市的ID阿
如果是省  因为省上一级别没上个级别了  索引存的0 阿 比如   湖南的ID 是111   长沙的ID 是 222   天心区的ID 是333  
那么数据库里的记录就是这样的
id                    fatherid        name
111                 0                   湖南
222                111               长沙
333                 222              天心区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 仅供参考
大牛  你这个写的太复杂了啊  我感觉只要  
  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 呢