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

hadoop333 2016-09-10 02:49:35
有一张区域分类表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
请教一下各位大牛如何实现呢
...全文
890 8 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
8 条回复
切换为时间正序
请发表友善的回复…
发表回复
hadoop333 2016-09-12
  • 打赏
  • 举报
回复
引用 3 楼 ghx287524027 的回复:
select from table connect by prior id = fatherid [quote=引用 2 楼 hadoop333 的回复:] [quote=引用 1 楼 ghx287524027 的回复:] ather_id为0时表示省份,怎么表示市和县
市和县都有具体的数字啊 比如是市的时候 市的父ID 那个列 就存上一个级别的的省的ID 阿 如果是县的的时候 县的父ID 那一个列 就存的上一个级别的 市的ID阿 如果是省 因为省上一级别没上个级别了 索引存的0 阿 比如 湖南的ID 是111 长沙的ID 是 222 天心区的ID 是333 那么数据库里的记录就是这样的 id fatherid name 111 0 湖南 222 111 长沙 333 222 天心区[/quote]
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 呢
hadoop333 2016-09-11
  • 打赏
  • 举报
回复
引用 4 楼 wmxcn2000 的回复:

-- 借楼上数据 

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 
版主 我感觉 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 呢
hadoop333 2016-09-11
  • 打赏
  • 举报
回复
引用 3 楼 ghx287524027 的回复:
select from table connect by prior id = fatherid [quote=引用 2 楼 hadoop333 的回复:] [quote=引用 1 楼 ghx287524027 的回复:] ather_id为0时表示省份,怎么表示市和县
市和县都有具体的数字啊 比如是市的时候 市的父ID 那个列 就存上一个级别的的省的ID 阿 如果是县的的时候 县的父ID 那一个列 就存的上一个级别的 市的ID阿 如果是省 因为省上一级别没上个级别了 索引存的0 阿 比如 湖南的ID 是111 长沙的ID 是 222 天心区的ID 是333 那么数据库里的记录就是这样的 id fatherid name 111 0 湖南 222 111 长沙 333 222 天心区[/quote]
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
卖水果的net 2016-09-11
  • 打赏
  • 举报
回复

-- 借楼上数据 

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 
卖水果的net 2016-09-11
  • 打赏
  • 举报
回复
引用 6 楼 hadoop333 的回复:
我感觉 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 呢
按你这个写法就可以了; PS:注意业务数据的规范;
ghx287524027 2016-09-10
  • 打赏
  • 举报
回复
select from table connect by prior id = fatherid
引用 2 楼 hadoop333 的回复:
[quote=引用 1 楼 ghx287524027 的回复:] ather_id为0时表示省份,怎么表示市和县
市和县都有具体的数字啊 比如是市的时候 市的父ID 那个列 就存上一个级别的的省的ID 阿 如果是县的的时候 县的父ID 那一个列 就存的上一个级别的 市的ID阿 如果是省 因为省上一级别没上个级别了 索引存的0 阿 比如 湖南的ID 是111 长沙的ID 是 222 天心区的ID 是333 那么数据库里的记录就是这样的 id fatherid name 111 0 湖南 222 111 长沙 333 222 天心区[/quote]
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 
仅供参考
hadoop333 2016-09-10
  • 打赏
  • 举报
回复
引用 1 楼 ghx287524027 的回复:
ather_id为0时表示省份,怎么表示市和县
市和县都有具体的数字啊 比如是市的时候 市的父ID 那个列 就存上一个级别的的省的ID 阿 如果是县的的时候 县的父ID 那一个列 就存的上一个级别的 市的ID阿 如果是省 因为省上一级别没上个级别了 索引存的0 阿 比如 湖南的ID 是111 长沙的ID 是 222 天心区的ID 是333 那么数据库里的记录就是这样的 id fatherid name 111 0 湖南 222 111 长沙 333 222 天心区
ghx287524027 2016-09-10
  • 打赏
  • 举报
回复
ather_id为0时表示省份,怎么表示市和县

17,140

社区成员

发帖
与我相关
我的任务
社区描述
Oracle开发相关技术讨论
社区管理员
  • 开发
  • Lucifer三思而后行
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧