create table BT_AUDIT_ITEM
(
ITEM_CODE VARCHAR2(30) not null,
ITEM_NAME VARCHAR2(50),
SUPER_ITEM_CODE VARCHAR2(30),
)insert into BT_AUDIT_ITEM values('0001','话费','0000')
insert into BT_AUDIT_ITEM values('0002','预存话费','0001')
insert into BT_AUDIT_ITEM values('0003','卡费','0000')
insert into BT_AUDIT_ITEM values('0004','SIM卡费','0003')
insert into BT_AUDIT_ITEM values('0005','开户预存款','0002')
insert into BT_AUDIT_ITEM values('0006','缴费预存款','0002')
insert into BT_AUDIT_ITEM values('0007','终端预存','0002')
insert into BT_AUDIT_ITEM values('0008','终端销售','0000')现在要查出所有3级选项和没有3级的2级,和没有2级的1级,也就是每条分支的最后一个元素,咋搞?
(
ITEM_CODE VARCHAR2(30) not null,
ITEM_NAME VARCHAR2(50),
SUPER_ITEM_CODE VARCHAR2(30),
)insert into BT_AUDIT_ITEM values('0001','话费','0000')
insert into BT_AUDIT_ITEM values('0002','预存话费','0001')
insert into BT_AUDIT_ITEM values('0003','卡费','0000')
insert into BT_AUDIT_ITEM values('0004','SIM卡费','0003')
insert into BT_AUDIT_ITEM values('0005','开户预存款','0002')
insert into BT_AUDIT_ITEM values('0006','缴费预存款','0002')
insert into BT_AUDIT_ITEM values('0007','终端预存','0002')
insert into BT_AUDIT_ITEM values('0008','终端销售','0000')现在要查出所有3级选项和没有3级的2级,和没有2级的1级,也就是每条分支的最后一个元素,咋搞?
解决方案 »
- ★急救★急救★,ORACLE删除数据后如何恢复!
- oracle双机热备坏了,该如何修复,点上系统,兄弟们邦帮下?
- 通过网络向数据库中的表大数据量的插入数据
- oracle监听报错。SOS......
- 关于lob字段数据的导出!~!~
- 数据库时间重叠的难题
- sql四舍五入问题
- 谁有OCI的资料阿?能不能给一点学习一下阿?
- 我是ORACLE初学,在表下面的HR,OE,OEM,CTXSYS等,这些下面的表是来干什么??
- 在ORACLE 有没有像SQL SERVER里的xp_cmdshell 的命令来启动WINDOWS里的执行文件吗
- RAC 集群问题 望进来提点
- linux下oracle的问题,高手请进
select distinct a.super_item_code from BT_AUDIT_ITEM a
connect by prior a.item_code=a.super_item_code
start with trim(a.super_item_code)='0000'
)
看看上面的 不知满足
SELECT *
FROM (SELECT a.*,level lv,
DENSE_RANK () OVER (PARTITION BY SUBSTR
(SYS_CONNECT_BY_PATH
(item_code,
','
),
1,
5
) ORDER BY LEVEL DESC)
rn
FROM bt_audit_item a
START WITH super_item_code = '0000'
CONNECT BY PRIOR item_code = super_item_code)
WHERE rn = 1结果Row# ITEM_CODE ITEM_NAME SUPER_ITEM_CODE LV RN1 0005 开户预存款 0002 3 1
2 0006 缴费预存款 0002 3 1
3 0007 终端预存 0002 3 1
4 0004 SIM卡费 0003 2 1
5 0008 终端销售 0000 1 1
看看是不你要的
SUBSTR (aaa.root, 1, 4) one, SUBSTR (aaa.root, 6, 4) two,
SUBSTR (aaa.root, 11, 4) three
FROM (SELECT aa.*, DENSE_RANK () OVER (PARTITION BY team ORDER BY lv DESC)
rn
FROM (SELECT a.*, LEVEL lv,
SUBSTR (SYS_CONNECT_BY_PATH (item_code, '-'),
2
) root,
SUBSTR (SYS_CONNECT_BY_PATH (item_code, '-'),
2,
4
) team
FROM bt_audit_item a
START WITH super_item_code = '0000'
CONNECT BY PRIOR item_code = super_item_code) aa) aaa
WHERE aaa.rn = 1
结果
Row# ITEM_CODE ITEM_NAME SUPER_ITEM_CODE ONE TWO THREE1 0005 开户预存款 0002 0001 0002 0005
2 0006 缴费预存款 0002 0001 0002 0006
3 0007 终端预存 0002 0001 0002 0007
4 0004 SIM卡费 0003 0003 0004
5 0008 终端销售 0000 0008
from (select temp.item_name as III,
bai.item_name as II,
bai.super_item_code
from (select *
from BT_AUDIT_ITEM b
where b.item_code not in
(select distinct a.super_item_code
from BT_AUDIT_ITEM a
connect by prior a.item_code = a.super_item_code
start with trim(a.super_item_code) = '0000')) temp
left outer join BT_AUDIT_ITEM bai on temp.super_item_code =
item_code) temp_ii
left outer join BT_AUDIT_ITEM bai_ii on temp_ii.super_item_code =
bai_ii.item_code
帮忙看看这个要怎么改
I II III
1 卡费 SIM卡费
2 话费 预存话费 开户预存款
3 话费 预存话费 缴费预存款
4 话费 预存话费 终端预存
5 终端销售二级和三级如果没有下级的话位置就不对了
1 卡费 SIM卡费
2 话费 预存话费 开户预存款
3 话费 预存话费 缴费预存款
4 话费 预存话费 终端预存
5 终端销售
上面位置错了,这个是结果
/* Formatted on 2008/07/03 13:29 (Formatter Plus v4.8.8) */
/* Formatted on 2008/07/03 13:29 (Formatter Plus v4.8.8) */
SELECT DECODE (LENGTH (root) - LENGTH (REPLACE (root, ',', '')),
0, NULL,
1, NULL,
SUBSTR (root, 1, INSTR (root, ',', 1, 1) - 1)
) I,
DECODE (LENGTH (root) - LENGTH (REPLACE (root, ',', '')),
0, NULL,
1, SUBSTR (root, 1, INSTR (root, ',', 1, 1) - 1),
SUBSTR (root,
INSTR (root, ',', 1, 1) + 1,
INSTR (root, ',', 1, 2) - INSTR (root, ',', 1, 1)-1
)
) II,
DECODE (LENGTH (root) - LENGTH (REPLACE (root, ',', '')),
0, root,
1, SUBSTR (root, INSTR (root, ',', 1, 1) + 1),
SUBSTR (root, INSTR (root, ',', 1, 2) + 1)
) III
FROM (SELECT b.*, SUBSTR (SYS_CONNECT_BY_PATH (item_name, ','), 2) root
FROM bt_audit_item b
WHERE b.item_code NOT IN (
SELECT DISTINCT a.super_item_code
FROM bt_audit_item a
CONNECT BY PRIOR a.item_code =
a.super_item_code
START WITH TRIM (a.super_item_code) =
'0000')
START WITH TRIM (b.super_item_code) = '0000'
CONNECT BY PRIOR b.item_code = b.super_item_code)结果Row# I II III1 话费 预存话费 开户预存款
2 话费 预存话费 缴费预存款
3 话费 预存话费 终端预存
4 卡费 SIM卡费
5 终端销售就是不知道你为什么要这样显示?
“终端销售”和“卡费”是1级的,应该在I下面 I II III
话费 预存话费 开户预存款
话费 预存话费 缴费预存款
话费 预存话费 终端预存
卡费 SIM卡费
终端销售
/* Formatted on 2008/07/03 13:29 (Formatter Plus v4.8.8) */
SELECT DECODE (LENGTH (root) - LENGTH (REPLACE (root, ',', '')),
0, root,
SUBSTR (root, 1, INSTR (root, ',', 1, 1) - 1)
) I,
DECODE (LENGTH (root) - LENGTH (REPLACE (root, ',', '')),
0, NULL,
1, SUBSTR (root, INSTR (root, ',', 1, 1) + 1),
SUBSTR (root,
INSTR (root, ',', 1, 1) + 1,
INSTR (root, ',', 1, 2) - INSTR (root, ',', 1, 1)-1
)
) II,
DECODE (LENGTH (root) - LENGTH (REPLACE (root, ',', '')),
0, null,
1, null,
SUBSTR (root, INSTR (root, ',', 1, 2) + 1)
) III
FROM (SELECT b.*, SUBSTR (SYS_CONNECT_BY_PATH (item_name, ','), 2) root
FROM bt_audit_item b
WHERE b.item_code NOT IN (
SELECT DISTINCT a.super_item_code
FROM bt_audit_item a
CONNECT BY PRIOR a.item_code =
a.super_item_code
START WITH TRIM (a.super_item_code) =
'0000')
START WITH TRIM (b.super_item_code) = '0000'
CONNECT BY PRIOR b.item_code = b.super_item_code)
结果Row# I II III1 话费 预存话费 开户预存款
2 话费 预存话费 缴费预存款
3 话费 预存话费 终端预存
4 卡费 SIM卡费
5 终端销售
你的语句比较费时啊,看执行计划Plan
SELECT STATEMENT ALL_ROWSCost: 12 Bytes: 166 Cardinality: 1
14 FILTER
5 HASH JOIN OUTER Cost: 10 Bytes: 2,656 Cardinality: 16
3 HASH JOIN OUTER Cost: 7 Bytes: 1,952 Cardinality: 16
1 TABLE ACCESS FULL TABLE EASYTVC_DEV.BT_AUDIT_ITEM Cost: 3 Bytes: 488 Cardinality: 8
2 TABLE ACCESS FULL TABLE EASYTVC_DEV.BT_AUDIT_ITEM Cost: 3 Bytes: 488 Cardinality: 8
4 TABLE ACCESS FULL TABLE EASYTVC_DEV.BT_AUDIT_ITEM Cost: 3 Bytes: 352 Cardinality: 8
13 FILTER
12 CONNECT BY WITH FILTERING
7 FILTER
6 TABLE ACCESS FULL TABLE EASYTVC_DEV.BT_AUDIT_ITEM Cost: 2 Bytes: 272 Cardinality: 8
10 HASH JOIN
8 CONNECT BY PUMP
9 TABLE ACCESS FULL TABLE EASYTVC_DEV.BT_AUDIT_ITEM Cost: 2 Bytes: 272 Cardinality: 8
11 TABLE ACCESS FULL TABLE EASYTVC_DEV.BT_AUDIT_ITEM Cost: 2 Bytes: 272 Cardinality: 8 COST=12我的语句(应该说在二楼的基础上改的)Plan
SELECT STATEMENT ALL_ROWSCost: 3 Bytes: 16,016 Cardinality: 8
17 VIEW EASYTVC_DEV. Cost: 3 Bytes: 16,016 Cardinality: 8
16 FILTER
7 CONNECT BY WITH FILTERING
2 FILTER
1 TABLE ACCESS FULL TABLE EASYTVC_DEV.BT_AUDIT_ITEM Cost: 3 Bytes: 488 Cardinality: 8
5 HASH JOIN
3 CONNECT BY PUMP
4 TABLE ACCESS FULL TABLE EASYTVC_DEV.BT_AUDIT_ITEM Cost: 3 Bytes: 488 Cardinality: 8
6 TABLE ACCESS FULL TABLE EASYTVC_DEV.BT_AUDIT_ITEM Cost: 3 Bytes: 488 Cardinality: 8
15 FILTER
14 CONNECT BY WITH FILTERING
9 FILTER
8 TABLE ACCESS FULL TABLE EASYTVC_DEV.BT_AUDIT_ITEM Cost: 3 Bytes: 272 Cardinality: 8
12 HASH JOIN
10 CONNECT BY PUMP
11 TABLE ACCESS FULL TABLE EASYTVC_DEV.BT_AUDIT_ITEM Cost: 3 Bytes: 272 Cardinality: 8
13 TABLE ACCESS FULL TABLE EASYTVC_DEV.BT_AUDIT_ITEM Cost: 3 Bytes: 272 Cardinality: 8 COST=3