没有具体结构,无法提供明确的语句。 大体思路是用 start with ........ connect by ...... 楼主可google oracle start with 相关内容以作参考
实测数据:CREATE TABLE kbm_dir ( ID NUMBER(4), Title VARCHAR2(20), Cod VARCHAR2(50), DirTyp VARCHAR2(20), DirProperty VARCHAR2(20) ); DELETE kbm_dir; INSERT INTO kbm_dir VALUES(1, 'A', '10', '00', NULL); INSERT INTO kbm_dir VALUES(2, 'AA', '1010', '00', NULL); INSERT INTO kbm_dir VALUES(3, 'AAA1', '101010', '00', NULL); INSERT INTO kbm_dir VALUES(4, 'AAA2', '101011', '01', '01'); INSERT INTO kbm_dir VALUES(5, 'AAA3', '101012', '01', '02');INSERT INTO kbm_dir VALUES(6, 'B', '11', '00', NULL); INSERT INTO kbm_dir VALUES(7, 'BB', '1110', '00', NULL); INSERT INTO kbm_dir VALUES(8, 'BBB1', '111010', '00', NULL); INSERT INTO kbm_dir VALUES(9, 'BBB2', '111011', '01', '01'); INSERT INTO kbm_dir VALUES(10, 'BBB3', '111012', '01', '02'); 实测结果:
建表:create table KBM_DIR ( ID INTEGER not null, COD VARCHAR2(30), DIRTYP VARCHAR2(10), TITLE VARCHAR2(100), PID VARCHAR2(30), --父级id TRADE VARCHAR2(10), DIRPROPERTY VARCHAR2(10), --目录属性 STATE NUMBER(2), ORD NUMBER(2) --当前级排序 )insert into KBM_DIR values(2044,'10','00','文件夹A','','00','','1','1'); insert into KBM_DIR values(2045,'1010','00','文件夹A01','2044','00','','1','1'); insert into KBM_DIR values(2047,'101010','00','文件夹A0101','2045','00','','1','1'); insert into KBM_DIR values(2049,'10101010','00','文件夹A010101','2047','00','','1','1'); insert into KBM_DIR values(2051,'1010101010','00','文件夹A01010101','2049','00','','1','1'); insert into KBM_DIR values(2053,'101010101010','00','文件夹A0101010101','2051','00','','1','1'); insert into KBM_DIR values(2054,'10101010101010','01','软件目录01','2053','00','01','1','1'); insert into KBM_DIR values(2055,'10101010101011','01','软件目录02','2053','00','01','1','2'); insert into KBM_DIR values(2052,'1010101011','00','文件夹A01010102','2049','00','','1','2'); insert into KBM_DIR values(2050,'10101011','00','文件夹A010102','2047','00','','1','2'); insert into KBM_DIR values(2048,'101011','00','文件夹A0102','2045','00','','1','2'); insert into KBM_DIR values(2046,'1011','00','文件夹A02','2044','00','','1','2'); insert into KBM_DIR values(2056,'101110','01','制度目录S01','2046','00','02','1','1'); insert into KBM_DIR values(2057,'101111','01','制度目录S02','2046','00','02','1','2'); insert into KBM_DIR values(2058,'11','00','文件夹B','','00','','1','2'); insert into KBM_DIR values(2059,'1110','00','文件夹B01','2058','00','','1','1'); insert into KBM_DIR values(2060,'111010','00','文件夹B0101','2059','00','','1','1'); insert into KBM_DIR values(2061,'11101010','00','文件夹B010101','2060','00','','1','1'); insert into KBM_DIR values(2062,'1110101010','00','文件夹B01010101','2061','00','','1','1'); insert into KBM_DIR values(2063,'111010101010','00','文件夹B0101010101','2062','00','','1','1'); insert into KBM_DIR values(2065,'11101010101010','01','制度目录1','2063','00','02','1','1'); insert into KBM_DIR values(2064,'111010101011','00','文件夹B0101010102','2062','00','','1','2'); insert into KBM_DIR values(2066,'11101010101110','01','新闻目录1','2064','00','03','1','1');
谢谢luiseradl,建表语句见5楼。我用这样的查询方法,select * from ( select id, title, cod, pid, dirtyp, dirProperty From kbm_dir where trade = '00' Start With dirProperty ='02' Connect By prior pid = id Order Siblings By ord )order by cod得到的结果中编码cod有重复,我是想按照cod(编码)进行树形结构,但有重复的就无法排列:
select * from kbm_dir start with dirProperty='02' connect by prior dirtyp = dirProperty
用distinct去掉重复的不行吗:select * from ( select distinct id, title, cod, pid, dirtyp, dirProperty From kbm_dir where trade = '00' Start With dirProperty ='02' Connect By prior pid = id Order Siblings By ord )order by cod;
大体思路是用 start with ........ connect by ...... 楼主可google oracle start with 相关内容以作参考
(
ID NUMBER(4),
Title VARCHAR2(20),
Cod VARCHAR2(50),
DirTyp VARCHAR2(20),
DirProperty VARCHAR2(20)
);
DELETE kbm_dir;
INSERT INTO kbm_dir VALUES(1, 'A', '10', '00', NULL);
INSERT INTO kbm_dir VALUES(2, 'AA', '1010', '00', NULL);
INSERT INTO kbm_dir VALUES(3, 'AAA1', '101010', '00', NULL);
INSERT INTO kbm_dir VALUES(4, 'AAA2', '101011', '01', '01');
INSERT INTO kbm_dir VALUES(5, 'AAA3', '101012', '01', '02');INSERT INTO kbm_dir VALUES(6, 'B', '11', '00', NULL);
INSERT INTO kbm_dir VALUES(7, 'BB', '1110', '00', NULL);
INSERT INTO kbm_dir VALUES(8, 'BBB1', '111010', '00', NULL);
INSERT INTO kbm_dir VALUES(9, 'BBB2', '111011', '01', '01');
INSERT INTO kbm_dir VALUES(10, 'BBB3', '111012', '01', '02');
实测结果:
(
ID INTEGER not null,
COD VARCHAR2(30),
DIRTYP VARCHAR2(10),
TITLE VARCHAR2(100),
PID VARCHAR2(30), --父级id
TRADE VARCHAR2(10),
DIRPROPERTY VARCHAR2(10), --目录属性
STATE NUMBER(2),
ORD NUMBER(2) --当前级排序
)insert into KBM_DIR values(2044,'10','00','文件夹A','','00','','1','1');
insert into KBM_DIR values(2045,'1010','00','文件夹A01','2044','00','','1','1');
insert into KBM_DIR values(2047,'101010','00','文件夹A0101','2045','00','','1','1');
insert into KBM_DIR values(2049,'10101010','00','文件夹A010101','2047','00','','1','1');
insert into KBM_DIR values(2051,'1010101010','00','文件夹A01010101','2049','00','','1','1');
insert into KBM_DIR values(2053,'101010101010','00','文件夹A0101010101','2051','00','','1','1');
insert into KBM_DIR values(2054,'10101010101010','01','软件目录01','2053','00','01','1','1');
insert into KBM_DIR values(2055,'10101010101011','01','软件目录02','2053','00','01','1','2');
insert into KBM_DIR values(2052,'1010101011','00','文件夹A01010102','2049','00','','1','2');
insert into KBM_DIR values(2050,'10101011','00','文件夹A010102','2047','00','','1','2');
insert into KBM_DIR values(2048,'101011','00','文件夹A0102','2045','00','','1','2');
insert into KBM_DIR values(2046,'1011','00','文件夹A02','2044','00','','1','2');
insert into KBM_DIR values(2056,'101110','01','制度目录S01','2046','00','02','1','1');
insert into KBM_DIR values(2057,'101111','01','制度目录S02','2046','00','02','1','2');
insert into KBM_DIR values(2058,'11','00','文件夹B','','00','','1','2');
insert into KBM_DIR values(2059,'1110','00','文件夹B01','2058','00','','1','1');
insert into KBM_DIR values(2060,'111010','00','文件夹B0101','2059','00','','1','1');
insert into KBM_DIR values(2061,'11101010','00','文件夹B010101','2060','00','','1','1');
insert into KBM_DIR values(2062,'1110101010','00','文件夹B01010101','2061','00','','1','1');
insert into KBM_DIR values(2063,'111010101010','00','文件夹B0101010101','2062','00','','1','1');
insert into KBM_DIR values(2065,'11101010101010','01','制度目录1','2063','00','02','1','1');
insert into KBM_DIR values(2064,'111010101011','00','文件夹B0101010102','2062','00','','1','2');
insert into KBM_DIR values(2066,'11101010101110','01','新闻目录1','2064','00','03','1','1');
select id, title, cod, pid, dirtyp, dirProperty From kbm_dir where trade = '00'
Start With dirProperty ='02'
Connect By prior pid = id
Order Siblings By ord
)order by cod得到的结果中编码cod有重复,我是想按照cod(编码)进行树形结构,但有重复的就无法排列:
select distinct id, title, cod, pid, dirtyp, dirProperty From kbm_dir where trade = '00'
Start With dirProperty ='02'
Connect By prior pid = id
Order Siblings By ord
)order by cod;