SQL connect by递归查询
来源:互联网 发布:淘宝聚星台在哪里 编辑:程序博客网 时间:2024/05/16 07:40
create table SYS_ADMINISTRATIVE_ORGAN( administrative_organ_id NUMBER(9) not null, administrative_organ_code VARCHAR2(10), administrative_organ_name VARCHAR2(100), remark VARCHAR2(200), parent_id NUMBER(9), syn_data_mark NUMBER(1))
-- Add comments to the table comment on table SYS_ADMINISTRATIVE_ORGAN is '行政区域表';-- Add comments to the columns comment on column SYS_ADMINISTRATIVE_ORGAN.administrative_organ_id is '行政区域ID';comment on column SYS_ADMINISTRATIVE_ORGAN.administrative_organ_code is '行政区域代码';comment on column SYS_ADMINISTRATIVE_ORGAN.administrative_organ_name is '行政区域名称';comment on column SYS_ADMINISTRATIVE_ORGAN.remark is '描述';comment on column SYS_ADMINISTRATIVE_ORGAN.parent_id is '上级行政区域ID';comment on column SYS_ADMINISTRATIVE_ORGAN.syn_data_mark is '数据同步标志(1:不同步)';--select * from SYS_ADMINISTRATIVE_ORGAN a where a.administrative_organ_name like '%山西%'
--select * from SYS_ADMINISTRATIVE_ORGAN a where a.parent_id=1
--select * from SYS_ADMINISTRATIVE_ORGAN a where a.administrative_organ_id=1
select Level ,
administrative_organ_id, a.parent_id, administrative_organ_name, lpad(' ', 8 * (Level - 1)) || administrative_organ_name as parentName
from SYS_ADMINISTRATIVE_ORGAN a
start with a.administrative_organ_id =1 connect by a.parent_id = prior a.administrative_organ_id
-- start with 语句表示树从什么位置开始进行检索
-- start with a.administrative_organ_id=1(从行政区划代码等于1的行政区划开始进行检索,即从“中华人民共和国”开始进行检索)
-- connect by语句在每行的行政区划前面加入空格
-- connect by语句表示当前数据行和下一行数据之间的关系。
-- prior 语句表示那个字段属于前一行(英文解释:优先的;在先的,在前的)
-- 如:connect by a.parent_id = prior a.administrative_organ_id;
-- 表示当前行的parent_id等于上一行的administrative_organ_id。
阅读全文
0 0
- SQL connect by递归查询
- Oracle 递归查询 树查询 start with SQL CONNECT BY
- Oracle PL/SQL之递归查询 - CONNECT BY PRIOR
- connect by prior树查询(递归查询)
- Sql级联查询递归查询start with 。。。connect by prior child=parent
- Start with ... Connect By 子句递归查询
- Start with ... Connect By 子句递归查询
- Oracle递归查询 start with Connect By
- Oracle 递归查询connect by priop 注解
- Oracle 递归查询connect by 简单例子
- oracle递归查询 start with...connect by
- oracle start with connect by递归查询
- Oracle 递归查询 Connect by prior
- connect by 递归子查询因子化
- 递归子查询因子化-CONNECT BY
- oracle connect by prior 递归查询
- 递归查询START WITH CONNECT BY PRIOR
- Oracle Connect By Prior(递归查询)
- Oracle中的存储过程简单例子
- ajax异步请求,生成表格,局部刷新
- list集合删除多个元素
- 编码问题 UnicodeDecodeError
- AVL树(二)之 C++的实现
- SQL connect by递归查询
- MYSQL模糊查询
- Codevs 1069 关押罪犯
- 【通用】正则表达式小记
- ThinkPHP-TPT360 文章分页不随搜索结果变化的问题
- 划分无冲突子集问题
- github 开源android项目
- opencv-性能测量与改进技术
- 用php二分法查找一个值在数组中的位置