环境:oracle 10g
执行以下查询.
select a.ID, a.Category, b.ChildCount
from tCategoryInfo a
left outer join (select Category, count(*) as ChildCount
from tTable
group by Category) b
on b.Category = a.Category
where a.category in
(
select c.Category
from (select Tablename,Category
from tTable
where Type in (0, 1, 2, 3, 5, 18, 20, 4, 17, 7, 22, 24, 26)
and Category =(select Category from tCategoryInfo where ID = a.id)
) c
left outer join
(select SrcTab
from tTable
where type in (1, 2, 12, 15, 21)
group by srctab
) d
on d.SrcTab = c.Tablename )
用rp表示这个结果集合 发现返回的结果集合等同于 select a.ID, a.Category, b.ChildCount
from tCategoryInfo a
left outer join (select Category, count(*) as ChildCount
from tTable
group by Category) b
on b.Category = a.Category
用RS 表示上面这个结果集合.
就是说RP=RS.
逻辑上来说,要么where 语句总是返回true,要么就是oracle哪里有什么玄机. 因为从RP来看当A.ID=1的时候,代入到where语句中,发现返回
的结果结合是空的,也就是说条件应该返回false,既然false,那么相应的记录也应该被过滤掉,则rs<>rp .
那么现在这样是怎么回事了?
执行以下查询.
select a.ID, a.Category, b.ChildCount
from tCategoryInfo a
left outer join (select Category, count(*) as ChildCount
from tTable
group by Category) b
on b.Category = a.Category
where a.category in
(
select c.Category
from (select Tablename,Category
from tTable
where Type in (0, 1, 2, 3, 5, 18, 20, 4, 17, 7, 22, 24, 26)
and Category =(select Category from tCategoryInfo where ID = a.id)
) c
left outer join
(select SrcTab
from tTable
where type in (1, 2, 12, 15, 21)
group by srctab
) d
on d.SrcTab = c.Tablename )
用rp表示这个结果集合 发现返回的结果集合等同于 select a.ID, a.Category, b.ChildCount
from tCategoryInfo a
left outer join (select Category, count(*) as ChildCount
from tTable
group by Category) b
on b.Category = a.Category
用RS 表示上面这个结果集合.
就是说RP=RS.
逻辑上来说,要么where 语句总是返回true,要么就是oracle哪里有什么玄机. 因为从RP来看当A.ID=1的时候,代入到where语句中,发现返回
的结果结合是空的,也就是说条件应该返回false,既然false,那么相应的记录也应该被过滤掉,则rs<>rp .
那么现在这样是怎么回事了?
我的语句可能太复杂了,那么简化成以下那样的来看,大概的意思还在.
select a.ID, a.Category, b.ChildCount
from tCategoryInfo a
left outer join b
on b.Category = a.Category
where a.category in
(
select c.Category
from ( ..where ID = a.id) c
left outer join
(select SrcTab......) d
on d.SrcTab = c.Tablename
)
where子查询引用了a表的id.
大家能不能讲讲oracle是如何执行的,比如说是先连接再过滤,或者...之类的.