SQL 使いやすい関数とCASE式
文字列連結
# || で文字列連結 select name, category, name || category as name_category from soft;
日付系
# 現在の日付を取得 select current_date; # 現在の時間 select current_time; # 現在の日時 select current_timestamp; # extract # 日付要素の切り出し 戻り値数値なので注意 select current_timestamp as now, extract(year from current_timestamp) as year, extract(month from current_timestamp) as month, extract(day from current_timestamp) as day, extract(hour from current_timestamp) as hour, extract(minute from current_timestamp) as minute, extract(second from current_timestamp) as second;
COALESCE (コアレス)
左から順に引数を見て、最初にNULLでない値を返す。
select coalesce (null, 1) as col_1, coalesce (null, 'abc', null) as col_2, coalesce (null, null, '12345678') as col_3;
LIKE (あいまい検索)
# 前方一致 select * from soft where name like '%a'; # 中間一致 select * from soft where name like '%a%'; # 後方一致 select * from soft where name like 'a%';
BETWEEN (範囲検索)
select * from soft where price between 200 and 1000; # 以下と同じ select * from soft where price >= 200 and price <= 1000;
IN (ORの省略形)
select * from soft where price in (200, 400, 800); # 以下と同じ select * from soft where price = 200 or where price = 400, where price = 800; # NOTにする select * from soft where price not in (200, 400, 800);
CASE式
条件分岐をする
# 構文 CASE WHEN <評価式> THEN <式> WHEN <評価式> THEN <式> WHEN <評価式> THEN <式> WHEN <評価式> THEN <式> ELSE <式> END
使う
select * , case when category = 'Editor' then 'Eから始まる' || category || 'ですよ' when category = 'IDE' then 'Iから始まる' || category || 'ですよ' else null end as if_category from soft;
※endを忘れないように。