-- 摘自Jonathan Lewis的CBO基础
-- 在解释查询变换时，举的一个小例子,看完觉得有点意思，与大家分享

两个数学家在在同学聚会上开始谈论他们的家庭，作为数学家，他们的谈话当然要含蓄点
甲：你有孩子么？
乙：是的，有3个女儿
甲：她们多大了
乙：如果把她们的年龄相乘，结果为36
甲：不够确切
乙：如果把她们的年龄相加，结果为这个房间的人数
甲：（环视整个房间后）仍不够确切
乙：我的大女儿有一只宠物仓鼠，它有一条木头腿
甲：（此时甲已经知道其三个女儿的年龄）那么，这两个两岁大的是双胞胎吗？

如何使用SQL来推导3个女儿的年龄呢？

SQL笔记之 子查询080812

-- 摘自Jonathan Lewis的CBO基础
-- 在解释查询变换时，举的一个小例子,看完觉得有点意思，与大家分享

两个数学家在在同学聚会上开始谈论他们的家庭，作为数学家，他们的谈话当然要含蓄点
甲：你有孩子么？
乙：是的，有3个女儿
甲：她们多大了
乙：如果把她们的年龄相乘，结果为36
甲：不够确切
乙：如果把她们的年龄相加，结果为这个房间的人数
甲：（环视整个房间后）仍不够确切
乙：我的大女儿有一只宠物仓鼠，它有一条木头腿
甲：（此时甲已经知道其三个女儿的年龄）那么，这两个两岁大的是双胞胎吗？

如何使用SQL来推导3个女儿的年龄呢？
首先列出所有可能的年龄：1-36个整数，构造一个表
with age_list as (
select rownum age
from all_objects
where rownum <= 36
),
1
2
...
36

其次根据乘积36列出可能的组合
product_check as (
select
age1.age as youngest,
age2.age as middle,
age3.age as 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 = (
select max(age) from age_list
)
),

如果你在SQL>下边看边演示，这时你大概看出端倪了
YOUNGEST MIDDLE OLDEST SUMMED PRODUCT
3 3 4 10 36
2 3 6 11 36
1 6 6 13 36
2 2 9 13 36
1 4 9 14 36
1 3 12 16 36
1 2 18 21 36
1 1 36 38 36

现在加上年龄之和等于房间内人数，而还不能确定
因此，在房间内人数应该在上述结果集中有重复行。
再加上大女儿的仓鼠，因此得出2 2 9 年龄组合。
完整的SQL如下：

with age_list as (
select rownum age
from all_objects
where rownum <= 36
),
product_check as (
select
age1.age as youngest,
age2.age as middle,
age3.age as 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 = (
select max(age) from age_list
)
),
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
;