一张表通过自连接 通过子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时表示省份，怎么表示市和县
sql函数： 多级树状目录-根据父ID查询出所有的子ID
一. 简介 比如说部门，有上级部门，是多级的，需要根据部门ID查询出所有该部门的子部门。 如果是2级，可以通过左连接联查同一张表即可。但层级数不确定的情况下，就得用sql函数或者存储过程来实现了。 二. sql脚本 数据脚本： SET NAMES utf8mb4; SET FOREIGN_KEY...
怎么用sql，条件筛选写出一个表与另一个表关联的数据和没关联的，两个表没有直接关系，通过中间表连接的
今天写项目遇到一个问题，就是有两张表A表，B表，还有一张A-B关系表，两张表的关系是通过A-B关系表一一对应的，现在我有个需求就是，我现在从A表出发，条件查询出来A表中跟B表关联的数据， 直接上SQL语句 SELECT di.id, di.CODE, di.device_name, di.camera_type, di.decive_contour, di.rg_group_i...
写出一条Sql语句，取出表A中的第31条到第40条记录。表A以自动增长的ID作为主键。（注意：ID可能不是连续的）
写出一条Sql语句，取出表A中的第31条到第40条记录。表A以自动增长的ID作为主键。（注意：ID可能不是连续的）。笔试的时候经常会出现这道题，网上的答案也是龙鱼混杂，今天自己实践了一下，找出了正确的答案。        第一种解法：        select top 10 * from tableA where ID not in (select top 30 ID from tableA
SQL的四种连接 ——内连接、左连接（左外连接）、右连接（右外连接）、全连接（全外连接）
在数据库中建立两张表，分别是 user 和 u user表： u表 关联字段user.u_id 和 u.parent_id来演示一下MySQL的 内连接、左连接（左外连接）、右连接（右外连接）、全连接（全外连接） 内连接 关键字：inner join on sql语句： select user.*,u.* from user inner join u on user.u_id=u.parent_id 运行结果： 分析： 内连接就是找两个表的交集 左连接（左外连接） 关键字：left joi
Java_SQL多表查询之连接查询、子查询
编译软件：Intellij IDEA 多表查询的常用查询为连接查询和子查询。 数据准备： use oa; -- 创建部门表 create table oa_dept( id int primary key auto_increment, name varchar(64) ); -- 创建员工表 create table oa_emp( id int primary key auto_increment, name varchar(64), gender char(10
Oracle

17,140

社区成员

55,259

社区内容

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

加载中

查看更多榜单
社区公告
暂无公告

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

+ 用AI写文章