「Puzzle65」の編集履歴（バックアップ）一覧はこちら

「Puzzle65」(2008/06/04 (水) 14:59:53) の最新版変更点

追加された行は緑色になります。

削除された行は赤色になります。

*問題
  product_id | low_age | high_age | product_price
 ------------+---------+----------+---------------
  Product1   |       5 |       15 |       20.0000
  Product1   |      16 |       60 |       18.0000
  Product1   |      65 |      150 |       17.0000
  Product2   |       1 |        5 |       20.0000
  Product2   |       6 |       70 |       25.0000
  Product2   |      71 |      150 |       40.0000

-全年齢に適合する製品リストを求める。

*satomiの解答
**考え方
-low_ageとhigh_ageの差がすべて1であれば全年齢に適合していると考えられる。
--一番内側の問合せで、low_ageより小さく、最も近いhigh_ageを求める
--low_ageとhigh_ageの差(age)とproduct_idを求める（p4）
--p4でnullでないものだけ残す
  product_id | age
 ------------+-----
  Product1   |   1
  Product1   |   5
  Product2   |   1
  Product2   |   1
--product_idでグループ化
--age(low_ageとhigh_ageの差)が1であればいいので、ageの合計は行数と等しくなるはず。そのためproduct_idのカウントと、p4のageの数が等しいものを求める。
**SQL文
 select p4.product_id
 from(select p2.product_id,p2.low_age-(select max(p3.high_age)
 from pricebyage p3
 where p2.product_id=p3.product_id
 and p2.low_age>p3.high_age
 group by p3.product_id)as age
 from pricebyage p2 )as p4
 where p4.age is not null
 group by p4.product_id
 having count(p4.product_id)=sum(p4.age);
**結果
  product_id
 ------------
  Product2

*chiakiの解答
**考え方
-「全年齢に適合」を「1～150歳に適合」すればよいと考えた。
-テーブルPriceByAgeをT1、T2として自己結合。
 T1のhigh_ageがT2のlow_ageより小さく、かつlow_age-1と等しくないときの、
 product_idとT1.low_age+T2.high_ageを求める。
-product_idでグループ化し、T1.low_age+T2.high_age=151であるときのproduct_idを求める。
**SQL文
 SELECT P.product_id
 FROM (SELECT T1.product_id, (T2.high_age + T1.low_age) AS num
       FROM PriceByAge T1, PriceByAge T2
       WHERE T1.high_age != T2.low_age - 1
       AND T1.high_age < T2.low_age) AS P
 WHERE P.num = 151
 GROUP BY P.product_id;

**結果
  product_id
 -----------
  Product2 

*問題
  product_id | low_age | high_age | product_price
 ------------+---------+----------+---------------
  Product1   |       5 |       15 |       20.0000
  Product1   |      16 |       60 |       18.0000
  Product1   |      65 |      150 |       17.0000
  Product2   |       1 |        5 |       20.0000
  Product2   |       6 |       70 |       25.0000
  Product2   |      71 |      150 |       40.0000

-全年齢に適合する製品リストを求める。

*satomiの解答
**考え方
-low_ageとhigh_ageの差がすべて1であれば全年齢に適合していると考えられる。
--一番内側の問合せで、low_ageより小さく、最も近いhigh_ageを求める
--low_ageとhigh_ageの差(age)とproduct_idを求める（p4）
--p4でnullでないものだけ残す
  product_id | age
 ------------+-----
  Product1   |   1
  Product1   |   5
  Product2   |   1
  Product2   |   1
--product_idでグループ化
--age(low_ageとhigh_ageの差)が1であればいいので、ageの合計は行数と等しくなるはず。そのためproduct_idのカウントと、p4のageの数が等しいものを求める。
**SQL文
 select p4.product_id
 from(select p2.product_id,p2.low_age-(select max(p3.high_age)
 from pricebyage p3
 where p2.product_id=p3.product_id
 and p2.low_age>p3.high_age
 group by p3.product_id)as age
 from pricebyage p2 )as p4
 where p4.age is not null
 group by p4.product_id
 having count(p4.product_id)=sum(p4.age);
**結果
  product_id
 ------------
  Product2

*chiakiの解答
**考え方
-「全年齢に適合」を「1～150歳に適合」すればよいと考えた。
-テーブルPriceByAgeをT1、T2として自己結合。
 T1のhigh_ageがT2のlow_ageより小さく、かつlow_age-1と等しくないときの、
 product_idとT1.low_age+T2.high_ageを求める。
-product_idでグループ化し、T1.low_age+T2.high_age=151であるときのproduct_idを求める。
**SQL文
 SELECT P.product_id
 FROM (SELECT T1.product_id, (T2.high_age + T1.low_age) AS num
       FROM PriceByAge T1, PriceByAge T2
       WHERE T1.high_age != T2.low_age - 1
       AND T1.high_age < T2.low_age
       AND T1.product_id = T2.product_id) AS P
 WHERE P.num = 151
 GROUP BY P.product_id;

**結果
  product_id
 -----------
  Product2