SELECT速習メモ
前回のエントリで作ったテーブルにたいしてクエリ発行します。 少しずつクエリを複雑にしていきます。
-- 全件取得 select * from room_resv; -- 件数取得 select count(*) from room_resv; -- 条件 select count(*) from room_resv where room_id='00X'; -- 条件 select count(*) from room_resv where room_id='00X' and name='bob'; -- 集約 select * from room_resv group by room_id; -- (MySQLだと可能) select room_id from room_resv group by room_id; select room_id, count(*) from room_resv group by room_id; select room_id, name, count(*) from room_resv group by room_id, name; -- 集約と条件 select room_id,count(*) from room_resv group by room_id having count(*)>6; -- CASE式 -- 仮想列 -- 午前午後の判断 select room_id ,resv_start_time ,case when resv_start_time < 1200 then '午前' else '午後' end from room_resv; -- 午前午後のデータ整合性確認 select room_id ,resv_start_time ,case when resv_start_time <= resv_end_time then '○' else '×' end from room_resv; -- 仮想列の条件 -- 午前午後のデータ整合性確認2 select room_id ,resv_start_time from room_resv where case when resv_start_time <= resv_end_time then 0 else 1 end = 0; -- where case when resv_start_time <= resv_end_time then FALSE -- else TRUE -- end; -- 行列転置 select case when room_id='00X' then room_id else '---' end as X ,case when room_id='00Y' then room_id else '---' end as y ,case when room_id='00Z' then room_id else '---' end as z from room_resv; -- 行列転置 select count(case when room_id='00X' then room_id else null end) as X ,count(case when room_id='00Y' then room_id else null end) as y ,count(case when room_id='00Z' then room_id else null end) as z from room_resv group by room_id; -- 行列転置 select sum(case when room_id='00X' then 1 else 0 end) as X ,sum(case when room_id='00Y' then 1 else 0 end) as y ,sum(case when room_id='00Z' then 1 else 0 end) as z from room_resv group by room_id; -- ならば -- 下記は関数の使い方の確認 select room_id ,resv_date ,dayofweek(resv_date) -- 1:mon --- 7:sun ,resv_start_time ,resv_end_time ,timediff(resv_end_time,resv_start_time) as diff -- 時刻計算 ,case when timediff(resv_end_time,resv_start_time) <= 100 then 'OK' else 'NG' end result from room_resv; -- 日曜日ならば2時間までOK、平日は1時間まで select room_id ,resv_date ,dayofweek(resv_date) -- 1:mon --- 7:sun ,timediff(resv_end_time,resv_start_time) as diff ,case when dayofweek(resv_date) = 7 then case when timediff(resv_end_time,resv_start_time) <= 200 then 'OK' else 'NG' end else case when timediff(resv_end_time,resv_start_time) <= 100 then 'OK' else 'NG' end end result from room_resv; -- すくなくとも一つ -- すくなくとも一つは2:30以上の予約がある部屋 select room_id from room_resv group by room_id having sum(case when timediff(resv_end_time,resv_start_time) >= 230 then 1 else 0 end) > 0; -- すべて -- すべては2:00以内の予約がある部屋 select room_id from room_resv group by room_id having sum(case when timediff(resv_end_time,resv_start_time) <= 200 then 1 else 0 end) = count(*);