Puzzle68 - SQLパズル演習

問題

次に出発するバスを見つける

Schedule(route_nbr,depart_time,arrive_time)

サンプルデータ：

route_nbr |    depart_time     |    arrive_time     |
----------+--------------------+--------------------+
3         | '2006-02-09 10:00' | '2006-02-09 14:00' 
4         | '2006-02-09 16:00' | '2006-02-09 17:00' 
5         | '2006-02-09 18:00' | '2006-02-09 19:00'
6         | '2006-02-09 20:00' | '2006-02-09 21:00'
7         | '2006-02-09 11:00' | '2006-02-09 13:00'
8         | '2006-02-09 15:00' | '2006-02-09 16:00'
9         | '2006-02-09 18:00' | '2006-02-09 20:00'

chiakiの解答

• 考え方

(1)待ち時間のわかるビューを作成する。
ただし、現在の時間よりも後にくるバスだけを出力。
(2)待ち時間の最も短く済むバスを出力させる。

• SQL文

CREATE VIEW Schedule2(route_nbr,wait_time)
AS SELECT route_nbr, depart_time-(timestamp '2006-02-09 15:30:00')
FROM Schedule
WHERE depart_time-(timestamp '2006-02-09 15:30:00') >= 0;

SELECT route_nbr
FROM Schedule2
WHERE wait_time <= ALL

(SELECT wait_time 
 FROM Schedule2);

• 結果

+------------+
| route_nbr  |
+------------+
|         4  |
+------------+

satomiの解答

考え方

• バス停に到着した時刻より後に出発するバスを求める
• その出発時刻を数値に変換
• 出発時刻が一番早いバスを選ぶ

SQL文

select D.route_nbr
from(select route_nbr,depart_time,
extract(HOUR_MINUTE  FROM depart_time)as hour1
from Schedule
where depart_time>='2006-02-09 15:30')
as D
order by hour1
limit 0,1;

結果

+-----------+
| route_nbr |
+-----------+
|         4 |
+-----------+

• EXTRACT(HOUR_MINUTE FROM depart_time)で、depart_timeの時分を数値に変換。

問題点

到着時間が16：30の時

+-----------+
| route_nbr |
+-----------+
|          5|
+-----------+

• limit0,1と定義しているため、一行しか出ない。