建表及数据脚本:
set feedback off
set define off
prompt Creating DEPARTMENT...
create table DEPARTMENT
(
DEPARTMENT_ID NUMBER(20) not null,
DEPT_NAME VARCHAR2(114) not null,
COMPANY_ID NUMBER(20),
PARENT_ID VARCHAR2(20),
POINT NUMBER(8,2) default 100 not null,
CREATE_PERSON VARCHAR2(20) not null,
CREATE_DATE DATE default SYSDATE not null,
UPDATE_PERSON VARCHAR2(20) not null,
UPDATE_DATE DATE default SYSDATE not null,
ORDER_ID VARCHAR2(10),
IS_DELETE VARCHAR2(1) default 'N' not null,
D_TREE_ID VARCHAR2(32)
);prompt Loading DEPARTMENT...
insert into DEPARTMENT (DEPARTMENT_ID, DEPT_NAME, COMPANY_ID, PARENT_ID, POINT, CREATE_PERSON, CREATE_DATE, UPDATE_PERSON, UPDATE_DATE, ORDER_ID, IS_DELETE, D_TREE_ID)
values (2, '一级公司', 14, '1', 100, 'sys', to_date('30-06-2008 17:15:43', 'dd-mm-yyyy hh24:mi:ss'), 'sys', to_date('21-10-2010 17:29:16', 'dd-mm-yyyy hh24:mi:ss'), null, 'N', null);
insert into DEPARTMENT (DEPARTMENT_ID, DEPT_NAME, COMPANY_ID, PARENT_ID, POINT, CREATE_PERSON, CREATE_DATE, UPDATE_PERSON, UPDATE_DATE, ORDER_ID, IS_DELETE, D_TREE_ID)
values (100, '公司领导', 14, '2', 142, 'Admin', to_date('01-12-2008 10:02:36', 'dd-mm-yyyy hh24:mi:ss'), 'sys', to_date('21-10-2010 17:29:16', 'dd-mm-yyyy hh24:mi:ss'), '001', 'N', null);
insert into DEPARTMENT (DEPARTMENT_ID, DEPT_NAME, COMPANY_ID, PARENT_ID, POINT, CREATE_PERSON, CREATE_DATE, UPDATE_PERSON, UPDATE_DATE, ORDER_ID, IS_DELETE, D_TREE_ID)
values (101, '行政部', 14, '2', 100, 'Admin', to_date('01-12-2008 10:02:48', 'dd-mm-yyyy hh24:mi:ss'), 'sys', to_date('21-10-2010 17:29:16', 'dd-mm-yyyy hh24:mi:ss'), '002', 'N', null);
insert into DEPARTMENT (DEPARTMENT_ID, DEPT_NAME, COMPANY_ID, PARENT_ID, POINT, CREATE_PERSON, CREATE_DATE, UPDATE_PERSON, UPDATE_DATE, ORDER_ID, IS_DELETE, D_TREE_ID)
values (102, '人力资源部', 14, '2', 100, 'Admin', to_date('01-12-2008 10:03:00', 'dd-mm-yyyy hh24:mi:ss'), 'sys', to_date('21-10-2010 17:29:16', 'dd-mm-yyyy hh24:mi:ss'), '003', 'N', null);
insert into DEPARTMENT (DEPARTMENT_ID, DEPT_NAME, COMPANY_ID, PARENT_ID, POINT, CREATE_PERSON, CREATE_DATE, UPDATE_PERSON, UPDATE_DATE, ORDER_ID, IS_DELETE, D_TREE_ID)
values (103, '财务部', 14, '2', 100, 'Admin', to_date('01-12-2008 10:03:14', 'dd-mm-yyyy hh24:mi:ss'), 'sys', to_date('21-10-2010 17:29:16', 'dd-mm-yyyy hh24:mi:ss'), '004', 'N', null);
insert into DEPARTMENT (DEPARTMENT_ID, DEPT_NAME, COMPANY_ID, PARENT_ID, POINT, CREATE_PERSON, CREATE_DATE, UPDATE_PERSON, UPDATE_DATE, ORDER_ID, IS_DELETE, D_TREE_ID)
values (104, '融资部', 14, '103', 100, 'Admin', to_date('01-12-2008 10:03:39', 'dd-mm-yyyy hh24:mi:ss'), 'sys', to_date('21-10-2010 17:29:16', 'dd-mm-yyyy hh24:mi:ss'), '00401', 'N', null);
insert into DEPARTMENT (DEPARTMENT_ID, DEPT_NAME, COMPANY_ID, PARENT_ID, POINT, CREATE_PERSON, CREATE_DATE, UPDATE_PERSON, UPDATE_DATE, ORDER_ID, IS_DELETE, D_TREE_ID)
values (105, '企业规划部', 14, '2', 100, 'Admin', to_date('01-12-2008 10:03:55', 'dd-mm-yyyy hh24:mi:ss'), 'sys', to_date('21-10-2010 17:29:16', 'dd-mm-yyyy hh24:mi:ss'), '005', 'N', null);
insert into DEPARTMENT (DEPARTMENT_ID, DEPT_NAME, COMPANY_ID, PARENT_ID, POINT, CREATE_PERSON, CREATE_DATE, UPDATE_PERSON, UPDATE_DATE, ORDER_ID, IS_DELETE, D_TREE_ID)
values (106, '信息技术部', 14, '105', 100, 'Admin', to_date('01-12-2008 10:04:11', 'dd-mm-yyyy hh24:mi:ss'), 'sys', to_date('21-10-2010 17:29:16', 'dd-mm-yyyy hh24:mi:ss'), '00501', 'N', null);
insert into DEPARTMENT (DEPARTMENT_ID, DEPT_NAME, COMPANY_ID, PARENT_ID, POINT, CREATE_PERSON, CREATE_DATE, UPDATE_PERSON, UPDATE_DATE, ORDER_ID, IS_DELETE, D_TREE_ID)
values (107, '商务部', 14, '2', 100, 'Admin', to_date('01-12-2008 10:04:28', 'dd-mm-yyyy hh24:mi:ss'), 'sys', to_date('21-10-2010 17:29:16', 'dd-mm-yyyy hh24:mi:ss'), '006', 'N', null);
insert into DEPARTMENT (DEPARTMENT_ID, DEPT_NAME, COMPANY_ID, PARENT_ID, POINT, CREATE_PERSON, CREATE_DATE, UPDATE_PERSON, UPDATE_DATE, ORDER_ID, IS_DELETE, D_TREE_ID)
values (108, '研究发展部', 14, '2', 100, 'Admin', to_date('01-12-2008 10:04:52', 'dd-mm-yyyy hh24:mi:ss'), 'sys', to_date('21-10-2010 17:29:16', 'dd-mm-yyyy hh24:mi:ss'), '007', 'N', null);
insert into DEPARTMENT (DEPARTMENT_ID, DEPT_NAME, COMPANY_ID, PARENT_ID, POINT, CREATE_PERSON, CREATE_DATE, UPDATE_PERSON, UPDATE_DATE, ORDER_ID, IS_DELETE, D_TREE_ID)
values (121, '董事会秘书处', 14, '2', 100, 'Admin', to_date('01-12-2008 10:25:10', 'dd-mm-yyyy hh24:mi:ss'), 'sys', to_date('21-10-2010 17:29:16', 'dd-mm-yyyy hh24:mi:ss'), '009', 'N', null);
insert into DEPARTMENT (DEPARTMENT_ID, DEPT_NAME, COMPANY_ID, PARENT_ID, POINT, CREATE_PERSON, CREATE_DATE, UPDATE_PERSON, UPDATE_DATE, ORDER_ID, IS_DELETE, D_TREE_ID)
values (123, '工程管理部', 14, '2', 100, 'Admin', to_date('01-12-2008 10:26:50', 'dd-mm-yyyy hh24:mi:ss'), 'sys', to_date('21-10-2010 17:29:16', 'dd-mm-yyyy hh24:mi:ss'), '010', 'N', null);
insert into DEPARTMENT (DEPARTMENT_ID, DEPT_NAME, COMPANY_ID, PARENT_ID, POINT, CREATE_PERSON, CREATE_DATE, UPDATE_PERSON, UPDATE_DATE, ORDER_ID, IS_DELETE, D_TREE_ID)
values (127, '安委办', 14, '123', 100, 'Admin', to_date('01-12-2008 10:28:43', 'dd-mm-yyyy hh24:mi:ss'), 'sys', to_date('21-10-2010 17:29:16', 'dd-mm-yyyy hh24:mi:ss'), '01001', 'N', null);
insert into DEPARTMENT (DEPARTMENT_ID, DEPT_NAME, COMPANY_ID, PARENT_ID, POINT, CREATE_PERSON, CREATE_DATE, UPDATE_PERSON, UPDATE_DATE, ORDER_ID, IS_DELETE, D_TREE_ID)
values (129, '内控与审计部', 14, '2', 100, 'Admin', to_date('01-12-2008 10:29:02', 'dd-mm-yyyy hh24:mi:ss'), 'sys', to_date('21-10-2010 17:29:16', 'dd-mm-yyyy hh24:mi:ss'), '011', 'N', null);
insert into DEPARTMENT (DEPARTMENT_ID, DEPT_NAME, COMPANY_ID, PARENT_ID, POINT, CREATE_PERSON, CREATE_DATE, UPDATE_PERSON, UPDATE_DATE, ORDER_ID, IS_DELETE, D_TREE_ID)
values (109, '海外业务部', 14, '2', 100, 'Admin', to_date('01-12-2008 10:05:21', 'dd-mm-yyyy hh24:mi:ss'), 'sys', to_date('21-10-2010 17:29:16', 'dd-mm-yyyy hh24:mi:ss'), '008', 'N', null);
insert into DEPARTMENT (DEPARTMENT_ID, DEPT_NAME, COMPANY_ID, PARENT_ID, POINT, CREATE_PERSON, CREATE_DATE, UPDATE_PERSON, UPDATE_DATE, ORDER_ID, IS_DELETE, D_TREE_ID)
values (571, 'trdt', 14, '102', 100, 'Admin', to_date('28-10-2010 12:56:46', 'dd-mm-yyyy hh24:mi:ss'), 'Admin', to_date('28-10-2010 12:56:46', 'dd-mm-yyyy hh24:mi:ss'), '2', 'Y', null);
commit;
prompt 16 records loaded
set feedback on
set define on
prompt Done.现在我要查询department_id = 104这个部门的上级部门层级关系,语句如下:
select * from department t
connect by prior t.parent_id = t.department_id
start with t.department_id = 104;
这个语句只查到这个部门的自身记录,没有查出上级部门,不知道问题出在哪,请大家帮忙看看吧!
set feedback off
set define off
prompt Creating DEPARTMENT...
create table DEPARTMENT
(
DEPARTMENT_ID NUMBER(20) not null,
DEPT_NAME VARCHAR2(114) not null,
COMPANY_ID NUMBER(20),
PARENT_ID VARCHAR2(20),
POINT NUMBER(8,2) default 100 not null,
CREATE_PERSON VARCHAR2(20) not null,
CREATE_DATE DATE default SYSDATE not null,
UPDATE_PERSON VARCHAR2(20) not null,
UPDATE_DATE DATE default SYSDATE not null,
ORDER_ID VARCHAR2(10),
IS_DELETE VARCHAR2(1) default 'N' not null,
D_TREE_ID VARCHAR2(32)
);prompt Loading DEPARTMENT...
insert into DEPARTMENT (DEPARTMENT_ID, DEPT_NAME, COMPANY_ID, PARENT_ID, POINT, CREATE_PERSON, CREATE_DATE, UPDATE_PERSON, UPDATE_DATE, ORDER_ID, IS_DELETE, D_TREE_ID)
values (2, '一级公司', 14, '1', 100, 'sys', to_date('30-06-2008 17:15:43', 'dd-mm-yyyy hh24:mi:ss'), 'sys', to_date('21-10-2010 17:29:16', 'dd-mm-yyyy hh24:mi:ss'), null, 'N', null);
insert into DEPARTMENT (DEPARTMENT_ID, DEPT_NAME, COMPANY_ID, PARENT_ID, POINT, CREATE_PERSON, CREATE_DATE, UPDATE_PERSON, UPDATE_DATE, ORDER_ID, IS_DELETE, D_TREE_ID)
values (100, '公司领导', 14, '2', 142, 'Admin', to_date('01-12-2008 10:02:36', 'dd-mm-yyyy hh24:mi:ss'), 'sys', to_date('21-10-2010 17:29:16', 'dd-mm-yyyy hh24:mi:ss'), '001', 'N', null);
insert into DEPARTMENT (DEPARTMENT_ID, DEPT_NAME, COMPANY_ID, PARENT_ID, POINT, CREATE_PERSON, CREATE_DATE, UPDATE_PERSON, UPDATE_DATE, ORDER_ID, IS_DELETE, D_TREE_ID)
values (101, '行政部', 14, '2', 100, 'Admin', to_date('01-12-2008 10:02:48', 'dd-mm-yyyy hh24:mi:ss'), 'sys', to_date('21-10-2010 17:29:16', 'dd-mm-yyyy hh24:mi:ss'), '002', 'N', null);
insert into DEPARTMENT (DEPARTMENT_ID, DEPT_NAME, COMPANY_ID, PARENT_ID, POINT, CREATE_PERSON, CREATE_DATE, UPDATE_PERSON, UPDATE_DATE, ORDER_ID, IS_DELETE, D_TREE_ID)
values (102, '人力资源部', 14, '2', 100, 'Admin', to_date('01-12-2008 10:03:00', 'dd-mm-yyyy hh24:mi:ss'), 'sys', to_date('21-10-2010 17:29:16', 'dd-mm-yyyy hh24:mi:ss'), '003', 'N', null);
insert into DEPARTMENT (DEPARTMENT_ID, DEPT_NAME, COMPANY_ID, PARENT_ID, POINT, CREATE_PERSON, CREATE_DATE, UPDATE_PERSON, UPDATE_DATE, ORDER_ID, IS_DELETE, D_TREE_ID)
values (103, '财务部', 14, '2', 100, 'Admin', to_date('01-12-2008 10:03:14', 'dd-mm-yyyy hh24:mi:ss'), 'sys', to_date('21-10-2010 17:29:16', 'dd-mm-yyyy hh24:mi:ss'), '004', 'N', null);
insert into DEPARTMENT (DEPARTMENT_ID, DEPT_NAME, COMPANY_ID, PARENT_ID, POINT, CREATE_PERSON, CREATE_DATE, UPDATE_PERSON, UPDATE_DATE, ORDER_ID, IS_DELETE, D_TREE_ID)
values (104, '融资部', 14, '103', 100, 'Admin', to_date('01-12-2008 10:03:39', 'dd-mm-yyyy hh24:mi:ss'), 'sys', to_date('21-10-2010 17:29:16', 'dd-mm-yyyy hh24:mi:ss'), '00401', 'N', null);
insert into DEPARTMENT (DEPARTMENT_ID, DEPT_NAME, COMPANY_ID, PARENT_ID, POINT, CREATE_PERSON, CREATE_DATE, UPDATE_PERSON, UPDATE_DATE, ORDER_ID, IS_DELETE, D_TREE_ID)
values (105, '企业规划部', 14, '2', 100, 'Admin', to_date('01-12-2008 10:03:55', 'dd-mm-yyyy hh24:mi:ss'), 'sys', to_date('21-10-2010 17:29:16', 'dd-mm-yyyy hh24:mi:ss'), '005', 'N', null);
insert into DEPARTMENT (DEPARTMENT_ID, DEPT_NAME, COMPANY_ID, PARENT_ID, POINT, CREATE_PERSON, CREATE_DATE, UPDATE_PERSON, UPDATE_DATE, ORDER_ID, IS_DELETE, D_TREE_ID)
values (106, '信息技术部', 14, '105', 100, 'Admin', to_date('01-12-2008 10:04:11', 'dd-mm-yyyy hh24:mi:ss'), 'sys', to_date('21-10-2010 17:29:16', 'dd-mm-yyyy hh24:mi:ss'), '00501', 'N', null);
insert into DEPARTMENT (DEPARTMENT_ID, DEPT_NAME, COMPANY_ID, PARENT_ID, POINT, CREATE_PERSON, CREATE_DATE, UPDATE_PERSON, UPDATE_DATE, ORDER_ID, IS_DELETE, D_TREE_ID)
values (107, '商务部', 14, '2', 100, 'Admin', to_date('01-12-2008 10:04:28', 'dd-mm-yyyy hh24:mi:ss'), 'sys', to_date('21-10-2010 17:29:16', 'dd-mm-yyyy hh24:mi:ss'), '006', 'N', null);
insert into DEPARTMENT (DEPARTMENT_ID, DEPT_NAME, COMPANY_ID, PARENT_ID, POINT, CREATE_PERSON, CREATE_DATE, UPDATE_PERSON, UPDATE_DATE, ORDER_ID, IS_DELETE, D_TREE_ID)
values (108, '研究发展部', 14, '2', 100, 'Admin', to_date('01-12-2008 10:04:52', 'dd-mm-yyyy hh24:mi:ss'), 'sys', to_date('21-10-2010 17:29:16', 'dd-mm-yyyy hh24:mi:ss'), '007', 'N', null);
insert into DEPARTMENT (DEPARTMENT_ID, DEPT_NAME, COMPANY_ID, PARENT_ID, POINT, CREATE_PERSON, CREATE_DATE, UPDATE_PERSON, UPDATE_DATE, ORDER_ID, IS_DELETE, D_TREE_ID)
values (121, '董事会秘书处', 14, '2', 100, 'Admin', to_date('01-12-2008 10:25:10', 'dd-mm-yyyy hh24:mi:ss'), 'sys', to_date('21-10-2010 17:29:16', 'dd-mm-yyyy hh24:mi:ss'), '009', 'N', null);
insert into DEPARTMENT (DEPARTMENT_ID, DEPT_NAME, COMPANY_ID, PARENT_ID, POINT, CREATE_PERSON, CREATE_DATE, UPDATE_PERSON, UPDATE_DATE, ORDER_ID, IS_DELETE, D_TREE_ID)
values (123, '工程管理部', 14, '2', 100, 'Admin', to_date('01-12-2008 10:26:50', 'dd-mm-yyyy hh24:mi:ss'), 'sys', to_date('21-10-2010 17:29:16', 'dd-mm-yyyy hh24:mi:ss'), '010', 'N', null);
insert into DEPARTMENT (DEPARTMENT_ID, DEPT_NAME, COMPANY_ID, PARENT_ID, POINT, CREATE_PERSON, CREATE_DATE, UPDATE_PERSON, UPDATE_DATE, ORDER_ID, IS_DELETE, D_TREE_ID)
values (127, '安委办', 14, '123', 100, 'Admin', to_date('01-12-2008 10:28:43', 'dd-mm-yyyy hh24:mi:ss'), 'sys', to_date('21-10-2010 17:29:16', 'dd-mm-yyyy hh24:mi:ss'), '01001', 'N', null);
insert into DEPARTMENT (DEPARTMENT_ID, DEPT_NAME, COMPANY_ID, PARENT_ID, POINT, CREATE_PERSON, CREATE_DATE, UPDATE_PERSON, UPDATE_DATE, ORDER_ID, IS_DELETE, D_TREE_ID)
values (129, '内控与审计部', 14, '2', 100, 'Admin', to_date('01-12-2008 10:29:02', 'dd-mm-yyyy hh24:mi:ss'), 'sys', to_date('21-10-2010 17:29:16', 'dd-mm-yyyy hh24:mi:ss'), '011', 'N', null);
insert into DEPARTMENT (DEPARTMENT_ID, DEPT_NAME, COMPANY_ID, PARENT_ID, POINT, CREATE_PERSON, CREATE_DATE, UPDATE_PERSON, UPDATE_DATE, ORDER_ID, IS_DELETE, D_TREE_ID)
values (109, '海外业务部', 14, '2', 100, 'Admin', to_date('01-12-2008 10:05:21', 'dd-mm-yyyy hh24:mi:ss'), 'sys', to_date('21-10-2010 17:29:16', 'dd-mm-yyyy hh24:mi:ss'), '008', 'N', null);
insert into DEPARTMENT (DEPARTMENT_ID, DEPT_NAME, COMPANY_ID, PARENT_ID, POINT, CREATE_PERSON, CREATE_DATE, UPDATE_PERSON, UPDATE_DATE, ORDER_ID, IS_DELETE, D_TREE_ID)
values (571, 'trdt', 14, '102', 100, 'Admin', to_date('28-10-2010 12:56:46', 'dd-mm-yyyy hh24:mi:ss'), 'Admin', to_date('28-10-2010 12:56:46', 'dd-mm-yyyy hh24:mi:ss'), '2', 'Y', null);
commit;
prompt 16 records loaded
set feedback on
set define on
prompt Done.现在我要查询department_id = 104这个部门的上级部门层级关系,语句如下:
select * from department t
connect by prior t.parent_id = t.department_id
start with t.department_id = 104;
这个语句只查到这个部门的自身记录,没有查出上级部门,不知道问题出在哪,请大家帮忙看看吧!
select * from department t
start with t.department_id = 104
connect by prior t.parent_id = t.department_id ;
--不可能,我查出3条数据:DEPARTMENT_ID DEPT_NAME COMPANY_ID PARENT_ID POINT CREATE_PERSON CREATE_DATE UPDATE_PERSON UPDATE_DATE ORDER_ID IS_DELETE D_TREE_ID
104 融资部 14 103 100 Admin 2008.12.01 10:03:39 sys 2010.10.21 17:29:16 00401 N
103 财务部 14 2 100 Admin 2008.12.01 10:03:14 sys 2010.10.21 17:29:16 004 N
2 一级公司 14 1 100 sys 2008.06.30 17:15:43 sys 2010.10.21 17:29:16 N
的确是数据类型问题,改成这样就可以了:
select * from department t
connect by prior to_number(t.parent_id) = t.department_id
start with t.department_id = 104;
已写入 file afiedt.buf 1 select department_id,DEPT_NAME,PARENT_ID,
2 sys_connect_by_path(DEPT_NAME,'/') 归属
3 from DEPARTMENT
4 start with department_id=104
5 connect by prior parent_id=department_id
6* order by PARENT_ID+0
SQL> /DEPARTMENT_ID DEPT_NAME PARENT_ID 归属
------------- ------------------------------ -------------------- ------------------------------
2 一级公司 1 /融资部/财务部/一级公司
103 财务部 2 /融资部/财务部
104 融资部 103 /融资部已用时间: 00: 00: 00.01
SQL> select * from department t
2 connect by prior t.parent_id = t.department_id
3 start with t.department_id = 104;DEPARTMENT_ID
-------------
DEPT_NAME
--------------------------------------------------------------------------------
COMPANY_ID PARENT_ID POINT CREATE_PERSON CREATE_DA
---------- -------------------- ---------- -------------------- ---------
UPDATE_PERSON UPDATE_DA ORDER_ID I D_TREE_ID
-------------------- --------- ---------- - --------------------------------
104
14 103 100 Admin 01-DEC-08
sys 21-OCT-10 00401 N
DEPARTMENT_ID
-------------
DEPT_NAME
--------------------------------------------------------------------------------
COMPANY_ID PARENT_ID POINT CREATE_PERSON CREATE_DA
---------- -------------------- ---------- -------------------- ---------
UPDATE_PERSON UPDATE_DA ORDER_ID I D_TREE_ID
-------------------- --------- ---------- - --------------------------------
103
14 2 100 Admin 01-DEC-08
sys 21-OCT-10 004 N
DEPARTMENT_ID
-------------
DEPT_NAME
--------------------------------------------------------------------------------
COMPANY_ID PARENT_ID POINT CREATE_PERSON CREATE_DA
---------- -------------------- ---------- -------------------- ---------
UPDATE_PERSON UPDATE_DA ORDER_ID I D_TREE_ID
-------------------- --------- ---------- - --------------------------------
2
һ˾
14 1 100 sys 30-JUN-08
sys 21-OCT-10 N楼主数据库的版本是?
现在已按照lnuwhy的方法,将parent_id的数据类型改成了NUMBER,和department_id一样,查询就正常了。
另外,wkc168的语句给了我另外一个知识点,也非常感谢。
谢谢大家!