読者です 読者をやめる 読者になる 読者になる

IT練習ノート

IT関連で調べたこと(実際は嵌ったこと)を書いています。

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(*);