SQL筆記之 子查詢080812

-- 摘自Jonathan Lewis的CBO基礎
-- 在解釋查詢變換時，舉的一個小例子,看完覺得有點意思，與大家分享

兩個數學家在在同學聚會上開始談論他們的家庭，作為數學家，他們的談話當然要含蓄點
甲：你有孩子麼？
乙：是的，有3個女兒
甲：她們多大了
乙：如果把她們的年齡相乘，結果為36
甲：不夠確切
乙：如果把她們的年齡相加，結果為這個房間的人數
甲：（環視整個房間後）仍不夠確切
乙：我的大女兒有一隻寵物倉鼠，它有一條木頭腿
甲：（此時甲已經知道其三個女兒的年齡）那麼，這兩個兩歲大的是雙胞胎嗎？

如何使用SQL來推導3個女兒的年齡呢？

[@more@]

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
;