Puzzle53 - SQLパズル演習

問題

以下のテーブルfoobarについて各列を底辺(5)から上へ見ていき、最初に見つかった非NULL値をとる
fumiko=# select * from foobar;

 lvl | color | length | width | hgt
-----+-------+--------+-------+-----
   1 | RED   |      8 |    10 |  12
   2 |       |        |       |  20
   3 |       |      9 |    82 |  25
   4 | BLUE  |        |    67 |
   5 | GRAY  |        |       |

結果

 color | length | width | hgt
-------+--------+-------+-----
 GRAY  |      9 |    67 |  25

yukaさんの回答

考え
NULLでない色・長さ・幅のlvlの最大値を求める。
求めた最大値とlvlが等しい行の色・長さ・幅をそれぞれ取り出す。

SQL文

select 	(select color from Foobar where lvl = F1.max) as color,
(select length from Foobar where lvl = F2.max) as length,
(select width from Foobar where lvl = F3.max) as width,
(select hgt from Foobar where lvl = F4.max) as hgt
from (select max(lvl) as max
     from Foobar
     where color IS NOT NULL) F1,
   (select max(lvl) as max
    from Foobar
    where length IS NOT NULL) F2,
   (select max(lvl) as max
    from Foobar
    where width IS NOT NULL) F3,
   (select max(lvl)as max
    from Foobar
    where hgt IS NOT NULL) F4;

結果

+-------+--------+-------+------+
| color | length | width | hgt  |
+-------+--------+-------+------+
| GRAY　|     9  |   67  |   25 |
+-------+--------+-------+------+

fumikoの回答

• 方針

①自己結合(条件 f1.lvl < f2.lvl)したビューを作る
②lvl値が大きいほど値数が多いことを利用して集計する

①ビュー作成

create view fooview(color,length,width,hgt) as
select f2.color,f2.length,f2.width,f2.hgt
from foobar f1,foobar f2
where f1.lvl < f2.lvl

できるビュー

 color | length | width | hgt
-------+--------+-------+-----
       |        |       |  20
       |      9 |    82 |  25
 BLUE  |        |    67 |
 GRAY  |        |       |
       |      9 |    82 |  25
 BLUE  |        |    67 |
 GRAY  |        |       |
 BLUE  |        |    67 |
 GRAY  |        |       |
 GRAY  |        |       |

②SQL文

select distinct
(select color
from fooview
group by color 
having count(color) >= all(select count(color)
                          from fooview
                          group by color)) as color,
(select length
from fooview
group by length 
having count(length) >= all(select count(length)
                          from fooview
                          group by length)) as length,
(select width
from fooview
group by width 
having count(width) >= all(select count(width)
                          from fooview
                          group by width)) as width,
(select hgt
from fooview
group by hgt 
having count(hgt) >= all(select count(hgt)
                          from fooview
                          group by hgt)) as hgt
from fooview

結果

 color | length | width | hgt
-------+--------+-------+-----
 GRAY  |      9 |    67 |  25