测试数据如下:CREATE TABLE org_member(
ID varchar2(10),
Name varchar2(16),
org_department_id varchar2(8),
org_account_id varchar2(8)
);INSERT INTO org_member(ID,Name,org_department_id,org_account_id) values('c001','张三','a01','b01');
INSERT INTO org_member(ID,Name,org_department_id,org_account_id) values('c002','李四','a04','b01');
INSERT INTO org_member(ID,Name,org_department_id,org_account_id) values('c003','王五','a05','b02');
INSERT INTO org_member(ID,Name,org_department_id,org_account_id) values('c004','陈丽','a06','b01');
INSERT INTO org_member(ID,Name,org_department_id,org_account_id) values('c005','刘勇','a07','b01');
INSERT INTO org_member(ID,Name,org_department_id,org_account_id) values('c006','赵东','a08','b01');
INSERT INTO org_member(ID,Name,org_department_id,org_account_id) values('c007','马晓','a02','b03');
INSERT INTO org_member(ID,Name,org_department_id,org_account_id) values('c008','宁可','a03','b04');COMMIT;CREATE TABLE org_department(
ID varchar2(10),
Name varchar2(16),
org_account_id varchar2(8)
path varchar2(100)
);INSERT INTO org_department(ID,Name,org_account_id,path) values('a01','财务部','b01','01.02');
INSERT INTO org_department(ID,Name,org_account_id,path) values('a02','人事部','b01','01.03');
INSERT INTO org_department(ID,Name,org_account_id,path) values('a03','业务部','b03','01.04');
INSERT INTO org_department(ID,Name,org_account_id,path) values('a04','综合科','b03','01.03.01');
INSERT INTO org_department(ID,Name,org_account_id,path) values('a05','综合科','b03','01.02.01');
INSERT INTO org_department(ID,Name,org_account_id,path) values('a06','后勤科','b03','01.04.01');
INSERT INTO org_department(ID,Name,org_account_id,path) values('a07','后勤科','b01','01.02.01');
INSERT INTO org_department(ID,Name,org_account_id,path) values('a08','出纳科','b01','01.02.02');COMMIT;CREATE TABLE org_account(
ID varchar2(10),
Name varchar2(16)
);INSERT INTO org_account(ID,Name) values('b01','华北区');
INSERT INTO org_account(ID,Name) values('b02','华东区');
INSERT INTO org_account(ID,Name) values('b03','华南区');
INSERT INTO org_account(ID,Name) values('b04','华西区');COMMIT;要实现的结果如下:ID NAME 单位名称 部门名称 二级部门名称
-------------------- ---------- -------------------------------- -------------------------------- --------------------------------
c001 张三 华北区 财务部
c002 李四 华北区 财务部 综合科
c003 王五 华东区 财务部 综合科
c004 陈丽 华北区 财务部 后勤科
c005 刘勇 华北区 财务部 后勤科
c006 赵东 华北区 财务部 出纳科
c007 马晓 华南区 人事部
c008 宁可 华西区 业务部需要特别说明的是,前二天曾开贴求解过,luoyoumou老大曾经给出过解决方案,的确测试数据可以通过,但是在实际生产环境还是不行,仔细阅读了解决方案,并进行了分析,老大的语句如下:SELECT om.ID, om.Name, oa.Name as "单位名称",
(case when od1.Name not like '%部' then od2.Name else od1.Name end) as "部门名称",
(case when od1.Name like '%科' then od1.Name else '' end) as "二级部门名称"
FROM org_member om left join org_account oa on oa.ID = om.org_account_id
left join org_department od1 on od1.ID = om.org_department_id
left join org_department od2 on substr('.'||od1.path||'.',instr('.'||od1.path||'.','.',1,1)+1,instr('.'||od1.path||'.','.',1,2)-instr('.'||od1.path||'.','.',1,1)-1)=substr(od2.ID,2)
ORDER BY om.ID;
上面的CASE 语句在实际环境中是行不通的,因为部门名称,并不全是以“部”或“科”作为结尾的,而是需要先想办法取到部门的PATH值,然后找到他的上一级PATH值,根据上级PATH值是一个“.”间隔,还是二个点间隔,如是一个点间隔,那么他的上一级很可能是一个单数,这样就不行了,需要的原则是如果PATH,有二个点"."间隔,则取上一级,如果是一个"."间隔,那么就取本级,不知我这样解释是否说明白了
ID varchar2(10),
Name varchar2(16),
org_department_id varchar2(8),
org_account_id varchar2(8)
);INSERT INTO org_member(ID,Name,org_department_id,org_account_id) values('c001','张三','a01','b01');
INSERT INTO org_member(ID,Name,org_department_id,org_account_id) values('c002','李四','a04','b01');
INSERT INTO org_member(ID,Name,org_department_id,org_account_id) values('c003','王五','a05','b02');
INSERT INTO org_member(ID,Name,org_department_id,org_account_id) values('c004','陈丽','a06','b01');
INSERT INTO org_member(ID,Name,org_department_id,org_account_id) values('c005','刘勇','a07','b01');
INSERT INTO org_member(ID,Name,org_department_id,org_account_id) values('c006','赵东','a08','b01');
INSERT INTO org_member(ID,Name,org_department_id,org_account_id) values('c007','马晓','a02','b03');
INSERT INTO org_member(ID,Name,org_department_id,org_account_id) values('c008','宁可','a03','b04');COMMIT;CREATE TABLE org_department(
ID varchar2(10),
Name varchar2(16),
org_account_id varchar2(8)
path varchar2(100)
);INSERT INTO org_department(ID,Name,org_account_id,path) values('a01','财务部','b01','01.02');
INSERT INTO org_department(ID,Name,org_account_id,path) values('a02','人事部','b01','01.03');
INSERT INTO org_department(ID,Name,org_account_id,path) values('a03','业务部','b03','01.04');
INSERT INTO org_department(ID,Name,org_account_id,path) values('a04','综合科','b03','01.03.01');
INSERT INTO org_department(ID,Name,org_account_id,path) values('a05','综合科','b03','01.02.01');
INSERT INTO org_department(ID,Name,org_account_id,path) values('a06','后勤科','b03','01.04.01');
INSERT INTO org_department(ID,Name,org_account_id,path) values('a07','后勤科','b01','01.02.01');
INSERT INTO org_department(ID,Name,org_account_id,path) values('a08','出纳科','b01','01.02.02');COMMIT;CREATE TABLE org_account(
ID varchar2(10),
Name varchar2(16)
);INSERT INTO org_account(ID,Name) values('b01','华北区');
INSERT INTO org_account(ID,Name) values('b02','华东区');
INSERT INTO org_account(ID,Name) values('b03','华南区');
INSERT INTO org_account(ID,Name) values('b04','华西区');COMMIT;要实现的结果如下:ID NAME 单位名称 部门名称 二级部门名称
-------------------- ---------- -------------------------------- -------------------------------- --------------------------------
c001 张三 华北区 财务部
c002 李四 华北区 财务部 综合科
c003 王五 华东区 财务部 综合科
c004 陈丽 华北区 财务部 后勤科
c005 刘勇 华北区 财务部 后勤科
c006 赵东 华北区 财务部 出纳科
c007 马晓 华南区 人事部
c008 宁可 华西区 业务部需要特别说明的是,前二天曾开贴求解过,luoyoumou老大曾经给出过解决方案,的确测试数据可以通过,但是在实际生产环境还是不行,仔细阅读了解决方案,并进行了分析,老大的语句如下:SELECT om.ID, om.Name, oa.Name as "单位名称",
(case when od1.Name not like '%部' then od2.Name else od1.Name end) as "部门名称",
(case when od1.Name like '%科' then od1.Name else '' end) as "二级部门名称"
FROM org_member om left join org_account oa on oa.ID = om.org_account_id
left join org_department od1 on od1.ID = om.org_department_id
left join org_department od2 on substr('.'||od1.path||'.',instr('.'||od1.path||'.','.',1,1)+1,instr('.'||od1.path||'.','.',1,2)-instr('.'||od1.path||'.','.',1,1)-1)=substr(od2.ID,2)
ORDER BY om.ID;
上面的CASE 语句在实际环境中是行不通的,因为部门名称,并不全是以“部”或“科”作为结尾的,而是需要先想办法取到部门的PATH值,然后找到他的上一级PATH值,根据上级PATH值是一个“.”间隔,还是二个点间隔,如是一个点间隔,那么他的上一级很可能是一个单数,这样就不行了,需要的原则是如果PATH,有二个点"."间隔,则取上一级,如果是一个"."间隔,那么就取本级,不知我这样解释是否说明白了
解决方案 »
- 有人知道是否Oracle集成了决策分析函数么?
- oracle升级后为什么表必须写上用户才可以
- 触发器 能否更新某个字段
- 请问ORACLE中有关于取农历得函数或是方法吗?
- 存储进程中,如何将long型变量转换成字符型?
- 一条sql语句在sql sever能执行,在oracle却不行,朋友过来看看阿
- 小弟用proc程序select或update时中文字段显示乱码,请问哪位大虾能指点!!!???
- 要死人了,帮忙,表空间不够。
- 请高手介绍一下连接oracle的各种接口性能
- 各位大神,帮帮忙,我写了个oracle触发器为什么只能insert不能update,delete,啊
- SQL Server与Oracle数据库在安全性上的异同
- 为什么主机和虚拟机能互相ping通,虚拟机能访问主机,但是主机不能访问虚拟机呢
left join org_department od2 on substr('.'||od1.path||'.',instr('.'||od1.path||'.','.',1,1)+1,instr('.'||od1.path||'.','.',1,2)-instr('.'||od1.path||'.','.',1,1)-1)=substr(od2.ID,2)没有搞明白为什么PATH值会和ID值进行比较,是不是写错了?
[TEST@orcl] SQL>SELECT T1.ID,
2 T1.NAME,
3 T4.NAME AS 单位名称,
4 T2.NAME AS 部门名称,
5 CASE WHEN T2.NAME = T3.NAME THEN NULL ELSE T3.NAME END AS 二级部门名称
6 FROM ORG_MEMBER T1, ORG_DEPARTMENT T2, ORG_DEPARTMENT T3, ORG_ACCOUNT T4
7 WHERE T2.PATH = SUBSTR(T3.PATH, 1, 5)
8 AND T1.ORG_DEPARTMENT_ID = T3.ID
9 AND T1.ORG_ACCOUNT_ID = T4.ID
10 ORDER BY T1.ID;ID NAME 单位名称 部门名称 二级部门名称
---------- ---------------- ---------------- ---------------- ----------------
c001 张三 华北区 财务部
c002 李四 华北区 人事部 综合科
c003 王五 华东区 财务部 综合科
c004 陈丽 华北区 业务部 后勤科
c005 刘勇 华北区 财务部 后勤科
c006 赵东 华北区 财务部 出纳科
c007 马晓 华南区 人事部
c008 宁可 华西区 业务部已选择8行。