现在想要返回下面的结果
------------表脚本
CREATE TABLE "DEMO"
( "NAME" VARCHAR2(50),
"CODE" VARCHAR2(50),
"LEVEL_TYPE" VARCHAR2(50),
"PARENTCODE" VARCHAR2(50),
"CENT" NUMBER
)
/
解决方案 »
- 求ORACLE SQL语句
- 在ArcGIS Engine中如何创建一个组件实现把创建和管理geodatabase的API进行封装
- 请教这样的集合如何查询?
- 在字符界面下用什么命令启动Server Manager,数据库是oracle 9i,系统win2003
- 不太懂数据库,请问如果想在库中增加很多条纪录(10万条),怎么做
- 中间层连接Oracle问题,出现多个Program为dllhost.exe的session?
- oracle 怎么注册成linux服务?也就是linux启动以后自动启动oracle数据库。
- 有哪些Oracle/db2特有的命令/用法,SQL Server不具备的?
- 关于DBMS_CRYPTO.HASH计算BLOB字段MD5的问题
- 将excel导入oracle,数据显示异常
- 更新主键时的update触发器
- 动态sql语句问题
把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