select a.tg_meanings, (select (select (select a.tg_name from dual) from dual) from dual) from tg_info a; 这是我做的一个测试,是可以成功的
(select a.id from tree a where b.tg_define_level = a.code and a.level_layer = 21) 表b了?这里是访问不到最外层的b,这个嵌套里最先执行的是这个。
select a.id from tree a where b.tg_define_level = a.code and a.level_layer = 21这里没B表的吧...
这个sql能说明问题吗? select a.tg_meanings, (select (select (select a.tg_name from dual) from dual) from dual) from tg_info a;看看执行计划吧,oracle对这样的sql已经进行优化过了,SQL> set autotrace traceonly SQL> select a.tg_meanings, 2 (select 3 (select 4 (select a.tg_name from dual) 5 from dual) 6 from dual) 7 from tg_info a;已选择17行。 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF 'A'实际上这个sql就是:select a.sale_num,a.tg_name from tg_info a ,楼主问题就是嵌套超出范围了,sql这么写明显有隐患的
SELECT b.tg_name FROM tree lvl0, tree lvl1, tree lvl2, tg_info b WHERE lvl0.level_layer = 21 AND lvl0.code = b.tg_define_level AND lvl0.id = lvl1.parent_id AND lvl1.level_layer = 22 AND lvl1.code = b.tg_operation_type AND lvl1.id = lvl2.parent_id AND lvl2.level_layer = 23 AND lvl2.code = b.tg_main_type
错了错了SELECT b.tg_name, lvl0.id AS pid FROM tree lvl0, tree lvl1, tree lvl2, tg_info b WHERE lvl0.level_layer = 21 AND lvl0.code = b.tg_define_level AND lvl0.id = lvl1.parent_id AND lvl1.level_layer = 22 AND lvl1.code = b.tg_operation_type AND lvl1.id = lvl2.parent_id AND lvl2.level_layer = 23 AND lvl2.code = b.tg_main_type
(select
(select
(select a.tg_name from dual)
from dual)
from dual)
from tg_info a;
这是我做的一个测试,是可以成功的
where b.tg_define_level = a.code and a.level_layer = 21这里没B表的吧...
select a.tg_meanings,
(select
(select
(select a.tg_name from dual)
from dual)
from dual)
from tg_info a;看看执行计划吧,oracle对这样的sql已经进行优化过了,SQL> set autotrace traceonly
SQL> select a.tg_meanings,
2 (select
3 (select
4 (select a.tg_name from dual)
5 from dual)
6 from dual)
7 from tg_info a;已选择17行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'A'实际上这个sql就是:select a.sale_num,a.tg_name from tg_info a ,楼主问题就是嵌套超出范围了,sql这么写明显有隐患的
FROM tree lvl0, tree lvl1, tree lvl2, tg_info b
WHERE lvl0.level_layer = 21
AND lvl0.code = b.tg_define_level
AND lvl0.id = lvl1.parent_id
AND lvl1.level_layer = 22
AND lvl1.code = b.tg_operation_type
AND lvl1.id = lvl2.parent_id
AND lvl2.level_layer = 23
AND lvl2.code = b.tg_main_type
FROM tree lvl0, tree lvl1, tree lvl2, tg_info b
WHERE lvl0.level_layer = 21
AND lvl0.code = b.tg_define_level
AND lvl0.id = lvl1.parent_id
AND lvl1.level_layer = 22
AND lvl1.code = b.tg_operation_type
AND lvl1.id = lvl2.parent_id
AND lvl2.level_layer = 23
AND lvl2.code = b.tg_main_type
互相学习 互相提高