子查詢分解

引自Johnathion的COB Fundamental:

數學家的對話:

“三個女兒年齡和36，年齡和是在場所有人的總數，大女兒又只寵物倉鼠”

with age_list as(
select rownum age from dba_objects where rownum <=36),
product_check as(
select age1.age youngest,
age2.age middle,
age3.age oldest,
age1.age + age2.age + age3.age as summed,
age1.age * age2.age * age3.age as product
from age_list age1
,age_list age2
,age_list age3
where age2.age >= age1.age
and age3.age >= age2.age
and age1.age * age2.age * age3.age =36)
,
summed_check as (
select youngest, middle, oldest, summed, product
from
( select youngest, middle, oldest, summed, product, count(*) over(partition by summed) ct
from product_check)
where ct > 1)
select * from summed_check
where oldest > middle
;

[@more@]