2-3-17 最小公倍数と最大公約数を求める

SQLパズル

10、15、45の最小公倍数(90)と最大公約数(5)を求める。

SQL

--■■■all_catalogデータディクショナリビューから連番を作成■■■
with WorkView as (
select 10 as Val from dual
union select 15 from dual
union select 45 from dual),
Renban as (select RowNum as i from all_catalog
            where RowNum <= (select Round(Exp(Sum(Ln(Val)))) from WorkView))
select
Min(case when 0 = all(select mod(i,Val) from WorkView)
    then i end) as 最小公倍数,
Max(case when 0 = all(select mod(Val,i) from WorkView)
    then i end) as 最大公約数
from Renban;

--■■■dualテーブルに対する階層問い合わせで連番を作成■■■
with WorkView as (
select 10 as Val from dual
union select 15 from dual
union select 45 from dual),
Renban as (select RowNum as i from dual
           connect by Level <= (select Round(Exp(Sum(Ln(Val)))) from WorkView))
select
Min(case when 0 = all(select mod(i,Val) from WorkView)
    then i end) as 最小公倍数,
Max(case when 0 = all(select mod(Val,i) from WorkView)
    then i end) as 最大公約数
from Renban;

解説

all述語とmod関数で、全て約数かチェックしてます。

---