トップページに戻る    次のSQLパズルへ    前のSQLパズルへ

11-2 ぶどうの房パズル上級編

SQLパズル

下の図での1〜15までの自然数の組み合わせを求めます。

ぶどうの房パズル
ぶどうの房パズル

SQL

col Val1 for 999
col Val2 for 999
col Val3 for 999
col Val4 for 999
col Val5 for 999
col Val6 for 999
col Val7 for 999
col Val8 for 999
col Val9 for 999
col Val10 for 9999
col Val11 for 9999
col Val12 for 9999
col Val13 for 9999
col Val14 for 9999
col Val15 for 9999

with WorkView as
(select 1 as Val from dual union select  2 from dual union select  3 from dual
union select   4 from dual union select  5 from dual union select  6 from dual
union select   7 from dual union select  8 from dual union select  9 from dual
union select  10 from dual union select 11 from dual union select 12 from dual
union select  13 from dual union select 14 from dual union select 15 from dual)
select a.Val as Val1,b.Val as Val2,c.Val as Val3,d.Val as Val4,e.Val as Val5,f.Val as Val6,
g.Val as Val7,h.Val as Val8,i.Val as Val9,j.Val as Val10,k.Val as Val11,l.Val as Val12,
m.Val as Val13,n.Val as Val14,o.Val as Val15
from
WorkView a,WorkView b,WorkView c,WorkView d,WorkView e,WorkView f,
WorkView g,WorkView h,WorkView i,WorkView j,WorkView k,WorkView l,
WorkView m,WorkView n,WorkView o
where a.Val not in(b.Val,c.Val,d.Val,e.Val,f.Val,g.Val,h.Val,i.Val,j.Val,k.Val,l.Val,m.Val,n.Val,o.Val)
  and b.Val not in(c.Val,d.Val,e.Val,f.Val,g.Val,h.Val,i.Val,j.Val,k.Val,l.Val,m.Val,n.Val,o.Val)
  and c.Val not in(d.Val,e.Val,f.Val,g.Val,h.Val,i.Val,j.Val,k.Val,l.Val,m.Val,n.Val,o.Val)
  and d.Val not in(e.Val,f.Val,g.Val,h.Val,i.Val,j.Val,k.Val,l.Val,m.Val,n.Val,o.Val)
  and e.Val not in(f.Val,g.Val,h.Val,i.Val,j.Val,k.Val,l.Val,m.Val,n.Val,o.Val)
  and f.Val not in(g.Val,h.Val,i.Val,j.Val,k.Val,l.Val,m.Val,n.Val,o.Val)
  and g.Val not in(h.Val,i.Val,j.Val,k.Val,l.Val,m.Val,n.Val,o.Val)
  and h.Val not in(i.Val,j.Val,k.Val,l.Val,m.Val,n.Val,o.Val)
  and i.Val not in(j.Val,k.Val,l.Val,m.Val,n.Val,o.Val)
  and j.Val not in(k.Val,l.Val,m.Val,n.Val,o.Val)
  and k.Val not in(l.Val,m.Val,n.Val,o.Val)
  and l.Val not in(m.Val,n.Val,o.Val)
  and m.Val not in(n.Val,o.Val)
  and n.Val != o.Val and
abs(a.Val-b.Val) = f.Val and abs(b.Val-c.Val) = g.Val and abs(c.Val-d.Val) = h.Val and
abs(d.Val-e.Val) = i.Val and abs(f.Val-g.Val) = j.Val and abs(g.Val-h.Val) = k.Val and
abs(h.Val-i.Val) = l.Val and abs(j.Val-k.Val) = m.Val and abs(k.Val-l.Val) = n.Val and
abs(m.Val-n.Val) = o.Val and
a.Val < e.Val;


解説

全ての組み合わせを求めて、abs関数を使って差のチェックをしてます。
where句でa.Val < e.Valをチェックして、線対称の形を排除してます。