设为首页 收藏本站
查看: 996|回复: 0

[经验分享] oracle_sql递归查询积累

[复制链接]

尚未签到

发表于 2016-7-19 12:17:50 | 显示全部楼层 |阅读模式
  一、start with.....connect by递归查询
    
DSC0000.jpg

建表语句:

CREATE TABLE D_ZONECODE
(
ID VARCHAR2(36) NOT NULL UNIQUE,
ZONECODE VARCHAR2(6) NOT NULL,
SUPERCODE VARCHAR2(6) NOT NULL,
ZONELLEVEL VARCHAR2(2) NOT NULL,
ZONENAME VARCHAR2(60) NOT NULL
);

  插入数据语句:

INSERT INTO D_ZONECODE (ID, ZONECODE, SUPERCODE, ZONELLEVEL, ZONENAME) VALUES ('1', '370000', '000000', '01', '山东省');
INSERT INTO D_ZONECODE (ID, ZONECODE, SUPERCODE, ZONELLEVEL, ZONENAME) VALUES ('2', '370100', '370000', '02', '济南市');
INSERT INTO D_ZONECODE (ID, ZONECODE, SUPERCODE, ZONELLEVEL, ZONENAME) VALUES ('3', '370102', '370100', '03', '历下区');
INSERT INTO D_ZONECODE (ID, ZONECODE, SUPERCODE, ZONELLEVEL, ZONENAME) VALUES ('4', '370103', '370100', '03', '市中区');
INSERT INTO D_ZONECODE (ID, ZONECODE, SUPERCODE, ZONELLEVEL, ZONENAME) VALUES ('5', '370104', '370100', '03', '槐荫区');
INSERT INTO D_ZONECODE (ID, ZONECODE, SUPERCODE, ZONELLEVEL, ZONENAME) VALUES ('6', '370105', '370100', '03', '天桥区');
INSERT INTO D_ZONECODE (ID, ZONECODE, SUPERCODE, ZONELLEVEL, ZONENAME) VALUES ('7', '370112', '370100', '03', '历城区');
INSERT INTO D_ZONECODE (ID, ZONECODE, SUPERCODE, ZONELLEVEL, ZONENAME) VALUES ('8', '370113', '370100', '03', '长清区');
INSERT INTO D_ZONECODE (ID, ZONECODE, SUPERCODE, ZONELLEVEL, ZONENAME) VALUES ('9', '370114', '370100', '03', '高新区');
INSERT INTO D_ZONECODE (ID, ZONECODE, SUPERCODE, ZONELLEVEL, ZONENAME) VALUES ('10', '370124', '370100', '03', '平阴县');
INSERT INTO D_ZONECODE (ID, ZONECODE, SUPERCODE, ZONELLEVEL, ZONENAME) VALUES ('11', '370125', '370100', '03', '济阳县');
INSERT INTO D_ZONECODE (ID, ZONECODE, SUPERCODE, ZONELLEVEL, ZONENAME) VALUES ('12', '370126', '370100', '03', '商河县');
INSERT INTO D_ZONECODE (ID, ZONECODE, SUPERCODE, ZONELLEVEL, ZONENAME) VALUES ('13', '370181', '370100', '03', '章丘市');

  
  root向树末梢查询:
  

select * from d_zonecode
start with id=’370000’
connect by prior zonecode = supercode;

  
  
DSC0001.jpg
  
 树末梢向ROOT查询:
  

select * from d_zonecode
start with zonecode = '370100'
connect by prior supercode = zonecode;

  
DSC0002.jpg
  
  附:
start with指明从哪里开始遍历树
connect by 就是指明父子关系,注重PRIOR位置
CONNECT_BY_ROOT: 提供获取根节点记录的字段信息。
  
  二、 今天客户突然要一个表格,这里面包括两个表的总分关系,比如我要显示部门同时和该部门下的所有人员用一条记录显示,在网上搜到一个例子,记录下方便使用。
  
  
DSC0003.jpg
 
DSC0004.jpg
 
  建表语句:
  

/* Create Tables */
CREATE TABLE DEPT
(
ID VARCHAR2(36) NOT NULL UNIQUE,
DEPTID VARCHAR2(4) NOT NULL UNIQUE,
DEPTNAME VARCHAR2(60) NOT NULL
);
CREATE TABLE D_USER
(
ID VARCHAR2(36) NOT NULL UNIQUE,
USERID VARCHAR2(6) NOT NULL UNIQUE,
USERNAME VARCHAR2(60) NOT NULL,
DEPTID VARCHAR2(4) NOT NULL
);

  
  插入数据语句:
  

-- 部门表
INSERT INTO dept (id, deptid, deptname) VALUES ('1', '0001', '市场部');
INSERT INTO dept (id, deptid, deptname) VALUES ('2', '0002', '开发部');
INSERT INTO dept (id, deptid, deptname) VALUES ('3', '0003', '项目部');
-- 用户表
INSERT INTO D_USER (id, userid, username, deptid) VALUES ('1', '100001', '张肃宁', '0001');
INSERT INTO D_USER (id, userid, username, deptid) VALUES ('2', '100002', '王济南', '0002');
INSERT INTO D_USER (id, userid, username, deptid) VALUES ('3', '100003', '赵临沂', '0001');
INSERT INTO D_USER (id, userid, username, deptid) VALUES ('4', '100004', '金淄博', '0003');
INSERT INTO D_USER (id, userid, username, deptid) VALUES ('5', '100005', '李德州', '0002');
INSERT INTO D_USER (id, userid, username, deptid) VALUES ('6', '100006', '周济宁', '0001');
INSERT INTO D_USER (id, userid, username, deptid) VALUES ('7', '100007', '姜潍坊', '0003');
INSERT INTO D_USER (id, userid, username, deptid) VALUES ('8', '100008', '万青岛', '0001');

  
  查询:
  

select * from dept t;
select * from d_user t;
  
  
DSC0005.jpg
 
DSC0006.jpg
 
 测试sql如下:
  

select username from (
select row_number() over(order by lv desc) id,username,deptid from (
select level lv, replace(sys_connect_by_path(username,','),',',',') username,deptid from(
select deptid,username,row_number() over(order by username) id from
(
select a.deptid,a.deptname,b.username
from dept a,d_user b
where a.deptid = b.deptid
)
) connect by prior id = id-1 )) where id = 1;

  
  
DSC0007.jpg
 
  要是需要和部门连接查询,我采用建立一个方法,然后查询部门记录时调用该方法,传部门id这个参数进去。
  
  
--方法建立:
create or replace function getUsername(oc_deptid in varchar2
) return varchar2 is
oc_result varchar(300);
oc_username varchar2(300); -- 取值
begin
--查询用户名称
select username into oc_result from (
select row_number() over(order by lv desc) id,username,deptid from (
select level lv, replace(sys_connect_by_path(username,','),',',',') username,deptid from(
select deptid,username,row_number() over(order by username) id from
(
select a.deptid,a.deptname,b.username
from dept a,d_user b
where a.deptid = b.deptid and a.deptid = oc_deptid
)
) connect by prior id = id-1 )) where id = 1;
oc_username:=oc_result;
if oc_result is NULL then
oc_username := '';
else
oc_username := SUBSTR(oc_result,INSTR(oc_result,',')+1,LENGTH(oc_result)-1);
end if;
return oc_username;
end;

  
  查询sql:
  


  

select t.deptid,t.deptname,getUsername(t.deptid) as username from dept t;
  
  
DSC0008.jpg
 
  附(CONNECT_BY_ROOT使用):
  

select zonecode,zonename,CONNECT_BY_ROOT(zonecode) as root_code  from d_zonecode
start with zonecode = '370100'
connect by prior zonecode = supercode and zonecode != supercode;
select zonecode,CONNECT_BY_ROOT(zonecode) as root_code from d_zonecode
start with zonecode = '370114'
connect by prior supercode = zonecode and zonecode != supercode;
select CONNECT_BY_ROOT(t.zonecode) as ROOT from d_zonecode t
where t.zonecode = '370100'
start with t.zonecode = t.supercode
connect by prior t.zonecode = t.supercode and t.zonecode != t.supercode;

  
  
  
DSC0009.jpg
 
DSC00010.jpg
 

运维网声明 1、欢迎大家加入本站运维交流群:群②:261659950 群⑤:202807635 群⑦870801961 群⑧679858003
2、本站所有主题由该帖子作者发表,该帖子作者与运维网享有帖子相关版权
3、所有作品的著作权均归原作者享有,请您和我们一样尊重他人的著作权等合法权益。如果您对作品感到满意,请购买正版
4、禁止制作、复制、发布和传播具有反动、淫秽、色情、暴力、凶杀等内容的信息,一经发现立即删除。若您因此触犯法律,一切后果自负,我们对此不承担任何责任
5、所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其内容的准确性、可靠性、正当性、安全性、合法性等负责,亦不承担任何法律责任
6、所有作品仅供您个人学习、研究或欣赏,不得用于商业或者其他用途,否则,一切后果均由您自己承担,我们对此不承担任何法律责任
7、如涉及侵犯版权等问题,请您及时通知我们,我们将立即采取措施予以解决
8、联系人Email:admin@iyunv.com 网址:www.yunweiku.com

所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其承担任何法律责任,如涉及侵犯版权等问题,请您及时通知我们,我们将立即处理,联系人Email:kefu@iyunv.com,QQ:1061981298 本贴地址:https://www.yunweiku.com/thread-246353-1-1.html 上篇帖子: Oracle 用户的相关操作 下篇帖子: oracle 闪回技术(行级)
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

扫码加入运维网微信交流群X

扫码加入运维网微信交流群

扫描二维码加入运维网微信交流群,最新一手资源尽在官方微信交流群!快快加入我们吧...

扫描微信二维码查看详情

客服E-mail:kefu@iyunv.com 客服QQ:1061981298


QQ群⑦:运维网交流群⑦ QQ群⑧:运维网交流群⑧ k8s群:运维网kubernetes交流群


提醒:禁止发布任何违反国家法律、法规的言论与图片等内容;本站内容均来自个人观点与网络等信息,非本站认同之观点.


本站大部分资源是网友从网上搜集分享而来,其版权均归原作者及其网站所有,我们尊重他人的合法权益,如有内容侵犯您的合法权益,请及时与我们联系进行核实删除!



合作伙伴: 青云cloud

快速回复 返回顶部 返回列表