试试笨办法 把LEVEL TYPE生成3个表,3个表左连接语句大致如下: SELECT A.NAME,A.CNT,B.NAME,B.CNT,C.NAME,C.CNT FROM (SELECT * FROM DEMO WHERE LEVEL_TYPE=1) A, (SELECT * FROM DEMO WHERE LEVEL_TYPE=2) B, (SELECT * FROM DEMO WHERE LEVEL_TYPE=3) C, WHERE A.CODE=B.PARENTCODE(+) AND B.CODE=C.PARENTCODE(+)
SELECT c.name "一级名称", c.cent "分数", b.name "二级名称", b.cent "分数", a.name "三级名称", a.cent "分数" FROM demo a, demo b, demo c WHERE a.level_type = '3' AND b.level_type = '2' AND c.level_type = '1' AND a.parentcode = b.code AND b.parentcode = c.code;
----------------------------数据脚本 1 "INSERT INTO demo (NAME,CODE,LEVEL_TYPE,PARENTCODE,CENT) VALUES('这是第一级','a','1','','100');" 2 "INSERT INTO demo (NAME,CODE,LEVEL_TYPE,PARENTCODE,CENT) VALUES('这是a下面的二级','a_a','2','a','55');" 3 "INSERT INTO demo (NAME,CODE,LEVEL_TYPE,PARENTCODE,CENT) VALUES('这是a的二级2','a_b','2','a','45');" 4 "INSERT INTO demo (NAME,CODE,LEVEL_TYPE,PARENTCODE,CENT) VALUES('这个是三级','a_a_a','3','a_a','55');" 5 "INSERT INTO demo (NAME,CODE,LEVEL_TYPE,PARENTCODE,CENT) VALUES('三级目录工','a_a_b','3','a_a','66');" 6 "INSERT INTO demo (NAME,CODE,LEVEL_TYPE,PARENTCODE,CENT) VALUES('三级我中国人','a_b_c','3','a_b','78');" 7 "INSERT INTO demo (NAME,CODE,LEVEL_TYPE,PARENTCODE,CENT) VALUES('工枯夺','b','1','','55');" 8 "INSERT INTO demo (NAME,CODE,LEVEL_TYPE,PARENTCODE,CENT) VALUES('六中为主中','b_a','2','b','56');" 9 "INSERT INTO demo (NAME,CODE,LEVEL_TYPE,PARENTCODE,CENT) VALUES('最后测试','b_a_a','3','b_a','45');"
笨方法啊select f.name1,f.cent1,f.name2,f.cent2,d.name as name3, d.cent as cent3from demo d, (select c.name1,c.cent1 ,b.name as name2, b.cent as cent2, b.code from demo b, (select a.name as name1,a.code,a.cent as cent1 from demo a where a.level_type = 1 ) c where b.level_type = 2 and b.parentcode = c.code) fwhere d.level_type = 3 and d.parentcode = f.code
把LEVEL TYPE生成3个表,3个表左连接语句大致如下:
SELECT
A.NAME,A.CNT,B.NAME,B.CNT,C.NAME,C.CNT
FROM
(SELECT * FROM DEMO WHERE LEVEL_TYPE=1) A,
(SELECT * FROM DEMO WHERE LEVEL_TYPE=2) B,
(SELECT * FROM DEMO WHERE LEVEL_TYPE=3) C,
WHERE A.CODE=B.PARENTCODE(+)
AND B.CODE=C.PARENTCODE(+)
c.cent "分数",
b.name "二级名称",
b.cent "分数",
a.name "三级名称",
a.cent "分数"
FROM demo a, demo b, demo c
WHERE a.level_type = '3'
AND b.level_type = '2'
AND c.level_type = '1'
AND a.parentcode = b.code
AND b.parentcode = c.code;
1 "INSERT INTO demo (NAME,CODE,LEVEL_TYPE,PARENTCODE,CENT)
VALUES('这是第一级','a','1','','100');"
2 "INSERT INTO demo (NAME,CODE,LEVEL_TYPE,PARENTCODE,CENT)
VALUES('这是a下面的二级','a_a','2','a','55');"
3 "INSERT INTO demo (NAME,CODE,LEVEL_TYPE,PARENTCODE,CENT)
VALUES('这是a的二级2','a_b','2','a','45');"
4 "INSERT INTO demo (NAME,CODE,LEVEL_TYPE,PARENTCODE,CENT)
VALUES('这个是三级','a_a_a','3','a_a','55');"
5 "INSERT INTO demo (NAME,CODE,LEVEL_TYPE,PARENTCODE,CENT)
VALUES('三级目录工','a_a_b','3','a_a','66');"
6 "INSERT INTO demo (NAME,CODE,LEVEL_TYPE,PARENTCODE,CENT)
VALUES('三级我中国人','a_b_c','3','a_b','78');"
7 "INSERT INTO demo (NAME,CODE,LEVEL_TYPE,PARENTCODE,CENT)
VALUES('工枯夺','b','1','','55');"
8 "INSERT INTO demo (NAME,CODE,LEVEL_TYPE,PARENTCODE,CENT)
VALUES('六中为主中','b_a','2','b','56');"
9 "INSERT INTO demo (NAME,CODE,LEVEL_TYPE,PARENTCODE,CENT)
VALUES('最后测试','b_a_a','3','b_a','45');"
(select c.name1,c.cent1 ,b.name as name2, b.cent as cent2, b.code
from demo b,
(select a.name as name1,a.code,a.cent as cent1 from demo a
where a.level_type = 1 ) c
where b.level_type = 2
and b.parentcode = c.code) fwhere d.level_type = 3
and d.parentcode = f.code