Puzzle65 - SQLパズル演習

問題

 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