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

11-3 サムライン

SQLパズル

■1マスに1から9までの数字のどれか1つをいれる
■ヒントの数字はその列の合計。1マスなら1桁、2マスなら2桁、3マスなら3桁の数と考える


横の合計 縦の合計 a 552 A 87 b 260 B 20 c 178 C 92786 d 273 D 61 e 640 E 83

サムライン


SQL

col Aa for 99
col Ba for 99
col Ca for 99
col Ea for 99
col Ab for 99
col Cb for 99
col Db for 99
col Eb for 99
col Bc for 99
col Cc for 99
col Dc for 99
col Ad for 99
col Bd for 99
col Cd for 99
col Ed for 99
col Ae for 99
col Ce for 99
col De for 99
col Ee for 99

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)
select Aa,Ba,Ca,Ea,
Ab,Cb,Db,Eb,
Bc,Cc,Dc,
Ad,Bd,Cd,Ed,
Ae,Ce,De,Ee
from
(select a.Val as Aa,b.Val as Ab,c.Val as Ad,d.Val as Ae,
e.Val as Ba,f.Val as Bc,g.Val as Bd,
h.Val as Ca,i.Val as Cb,j.Val as Cc,k.Val as Cd,l.Val as Ce,
m.Val as Db,n.Val as Dc,o.Val as De,
p.Val as Ea,q.Val as Eb,r.Val as Ed,s.Val as Ee
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,WorkView p,WorkView q,WorkView r,WorkView s)
where 100*Aa+10*Ba+Ca+Ea=552
  and Ab+100*Cb+10*Db+Eb=260
  and 100*Bc+10*Cc+Dc=178
  and 100*Ad+10*Bd+Cd+Ed=273
  and Ae+100*Ce+10*De+Ee=640
  and 10*Aa+Ab+10*Ad+Ae=87
  and Ba+Bc*10+Bd=20
  and 10000*Ca+1000*Cb+100*Cc+10*Cd+Ce=92786
  and 10*Db+Dc+De=61
  and 10*Ea+Eb+10*Ed+Ee=83;


解説

全ての組み合わせを求めて、where句で合計をチェックしてます。