10-217 古のSQLでcase式を代用

SQLパズル

select case
       when months_between(sysdate,birth_dt) between 168 and 216 then 1 --14 to 18
       when months_between(sysdate,birth_dt) between 217 and 252 then 2 --18 to 21
       when months_between(sysdate,birth_dt) between 253 and 780 then 3 --21 to 65
       when months_between(sysdate,birth_dt) > 780 then 4 --older than 65
       else null end as AgeGrade
from mw_clients
order by birth_dt;

上記のselect文をcase式を使わないで書き換える。

出力結果
AgeGrade
-------
      4
      3
      2
      1
   null

こちらを参考にさせていただきました(英語)

データ作成スクリプト

create table mw_clients(birth_dt) as
select add_months(sysdate,-100) from dual union
select add_months(sysdate,-200) from dual union
select add_months(sysdate,-220) from dual union
select add_months(sysdate,-400) from dual union
select add_months(sysdate,-800) from dual;

SQL

--■■■decode関数を使う方法■■■
select
decode(Least(months_between(sysdate , birth_dt),168),168,
decode(greatest(months_between(sysdate , birth_dt),216),216,1,0),0)+
decode(Least(months_between(sysdate , birth_dt),217),217,
decode(greatest(months_between(sysdate , birth_dt),252),252,2,0),0)+
decode(Least(months_between(sysdate , birth_dt),253),253,
decode(greatest(months_between(sysdate , birth_dt),780),780,3,0),0)+
decode(Least(months_between(sysdate , birth_dt),781),781,4,0)+
decode(greatest(months_between(sysdate , birth_dt),167),167,null,0) as AgeGrade
  from mw_clients
order by birth_dt;

--■■■スカラー副問い合わせを使う方法■■■
select
(select 1 from dual where months_between(sysdate , birth_dt) between 168 and 216 union all
 select 2 from dual where months_between(sysdate , birth_dt) between 217 and 252 union all
 select 3 from dual where months_between(sysdate , birth_dt) between 253 and 780 union all
 select 4 from dual where months_between(sysdate , birth_dt) > 780) as AgeGrade
  from mw_clients
order by birth_dt;

解説

decode関数を使う方法では、
X+0 = X
という数学の法則を使ってます。

7-15 古のSQL(条件分岐その1)

---