新开帖，接着求解一个难题

[ejren]

测试数据如下：

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，有二个点"."间隔，则取上一级，如果是一个"."间隔，那么就取本级，不知我这样解释是否说明白了

[ejren]

并且最后一句

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值进行比较，是不是写错了？

[BenChiM888]

[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行。

[tulang729]

想说的是，这个数据库设计有点不太合理吧，怎么都不设置主键和外键。
LZ可以说下 你们为什么要这样设计？

[d604060189]

围观2楼

[狂想者]

不懂，学习了！！！！

[yuzhong218]

不懂，你举个例子，比如说path值为：01.03.01的上一级path值是什么啊？

[新丁11111]

关注了。。。。。