[NaruTo の計算機譜表工房]
[NaruTo の 御家]

SQL を使って覆面算を解く

  1. 初めに

    2. 　とあるところで覆面算の問題が出た。
    MAN
    +WOMAN
    CHILD

    　チャレンジしてみたが、場合分けが多くて挫折。

    こんなんやってられるかぁ・・・ってんで コンピュータにやらせることに。

    　さて何が手っとり早い？ ・・・とりあえず、SQL がいいか。

  2. 解いてみる

    3. 　そういうわけで解いていく。

    1. 使うDatabase

      2. 　とりあえず、MS ACCESS 2003を使う。
      SQL文中の文字列結合演算子を & から + にすると MS SQL Server 2000 で動くと思います。

    2. 準備

      3. 　まず、数のリストを持つテーブルを作成する。
      create table NumList(num integer primary key)
      で、ここに 0〜9 の値を入れる。 
      
INSERT INTO NumList(num) VALUES(0)
INSERT INTO NumList(num) VALUES(1)
INSERT INTO NumList(num) VALUES(2)
INSERT INTO NumList(num) VALUES(3)
INSERT INTO NumList(num) VALUES(4)
INSERT INTO NumList(num) VALUES(5)
INSERT INTO NumList(num) VALUES(6)
INSERT INTO NumList(num) VALUES(7)
INSERT INTO NumList(num) VALUES(8)
INSERT INTO NumList(num) VALUES(9)
      順番に実行してください。
      テーブルに手で値を入れてもかまわないです。
      　まぁ、ここですぐに問題を解く SQL 文を作成しにいってもいいのだが
      いろいろ使えるので文字のリストも作っておく。
      
CREATE TABLE CharList(c char(1) primary key)
INSERT INTO CharList(c) VALUES('A')
INSERT INTO CharList(c) VALUES('C')
INSERT INTO CharList(c) VALUES('D')
INSERT INTO CharList(c) VALUES('H')
INSERT INTO CharList(c) VALUES('I')
INSERT INTO CharList(c) VALUES('L')
INSERT INTO CharList(c) VALUES('M')
INSERT INTO CharList(c) VALUES('N')
INSERT INTO CharList(c) VALUES('O')
INSERT INTO CharList(c) VALUES('W')

    3. SQL 作成

      4. 　さて、問題を解く SQL文を作成しに行く。 まず、 FROM句。 
      
NumList AS A,
      みたいなのを並べればいい。 ここで文字リストを使って 
      
SELECT 'NumList AS ' & c & ',' AS [FROM] FROM CharList
      としてコピペ。 
      
FROM
	NumList AS A,
	NumList AS C,
	NumList AS D,
	NumList AS H,
	NumList AS I,
	NumList AS L,
	NumList AS M,
	NumList AS N,
	NumList AS O,
	NumList AS W
      最後のコンマは手で消した。 　続いて 数式の条件。べたがき。 
      
WHERE
	((100 * M.num + 10 * A.num + N.num)
	+ (10000 * W + 1000 * O + 100 * M.num + 10 * A.num + N.num)
	= (10000 * C + 1000 * H + 100 * I.num + 10 * L.num + D.num))
      で、SELECT句。 
      
SELECT
	A.NumList AS A,
	C.NumList AS C,
	D.NumList AS D,
	H.NumList AS H,
	I.NumList AS I,
	L.NumList AS L,
	M.NumList AS M,
	N.NumList AS N,
	O.NumList AS O,
	W.NumList AS W
      でもいいのだがいまいちセンスがないのでやはりこうする。 
      
SELECT
	(100 * M.num + 10 * A.num + N.num) AS MAN,
	'+' AS [PLUS],
	(10000 * W + 1000 * O + 100 * M.num + 10 * A.num + N.num) AS WOMAN,
	'=' AS [EQUAL],
	(10000 * C + 1000 * H + 100 * I.num + 10 * L.num + D.num) AS CHILD
      全部あわせて 
      
SELECT
	(100 * M.num + 10 * A.num + N.num) AS MAN,
	'+' AS [PLUS],
	(10000 * W + 1000 * O + 100 * M.num + 10 * A.num + N.num) AS WOMAN,
	'=' AS [EQUAL],
	(10000 * C + 1000 * H + 100 * I.num + 10 * L.num + D.num) AS CHILD
FROM
	NumList AS A,
	NumList AS C,
	NumList AS D,
	NumList AS H,
	NumList AS I,
	NumList AS L,
	NumList AS M,
	NumList AS N,
	NumList AS O,
	NumList AS W
WHERE
	((100 * M.num + 10 * A.num + N.num)
	+ (10000 * W.num + 1000 * O.num + 100 * M.num + 10 * A.num + N.num)
	= (10000 * C.num + 1000 * H.num + 100 * I.num + 10 * L.num + D.num))
      さぁ、これで実行!・・・なんてしたらひどい目に遭う。
      ほとんどの文字が 0 の場合とかでたくさんの解がでてきて帰って来なくなる。
      覆面算の隠れた条件として
      • 一番上の桁は 0 でない
      • 異なる文字は異なる数値
      とりあえず、一つ目の条件を加える。
      
SELECT
	(100 * M.num + 10 * A.num + N.num) AS MAN,
	'+' AS [PLUS],
	(10000 * W.num + 1000 * O.num + 100 * M.num + 10 * A.num + N.num) AS WOMAN,
	'=' AS [EQUAL],
	(10000 * C.num + 1000 * H.num + 100 * I.num + 10 * L.num + D.num) AS CHILD
FROM
	NumList AS A,
	NumList AS C,
	NumList AS D,
	NumList AS H,
	NumList AS I,
	NumList AS L,
	NumList AS M,
	NumList AS N,
	NumList AS O,
	NumList AS W
WHERE
	((100 * M.num + 10 * A.num + N.num)
	+ (10000 * W.num + 1000 * O.num + 100 * M.num + 10 * A.num + N.num)
	= (10000 * C.num + 1000 * H.num + 100 * I.num + 10 * L.num + D.num))
	AND (M.num>=1) AND (W.num >= 1) AND (C.num>=1)
      で、二つ目の条件
      。
      べたうちはしんどい・・・このために CharList を作ったのである。
      
SELECT 
	'AND (' & L.c & '.num <> ' & R.c & '.num)'
	FROM
	CharList AS L,
	CharList AS R
	WHERE
	L.c < R.c
      とすればあっというまに次の条件式ができあがる 
      
	AND (O.num <> W.num)
	AND (M.num <> W.num)
	AND (A.num <> W.num)
	AND (N.num <> W.num)
	AND (C.num <> W.num)
	AND (H.num <> W.num)
	AND (I.num <> W.num)
	AND (L.num <> W.num)
	AND (D.num <> W.num)
	AND (M.num <> O.num)
	AND (A.num <> O.num)
	AND (N.num <> O.num)
	AND (C.num <> O.num)
	AND (H.num <> O.num)
	AND (I.num <> O.num)
	AND (L.num <> O.num)
	AND (D.num <> O.num)
	AND (A.num <> M.num)
	AND (C.num <> M.num)
	AND (H.num <> M.num)
	AND (I.num <> M.num)
	AND (L.num <> M.num)
	AND (D.num <> M.num)
	AND (M.num <> N.num)
	AND (A.num <> N.num)
	AND (C.num <> N.num)
	AND (H.num <> N.num)
	AND (I.num <> N.num)
	AND (L.num <> N.num)
	AND (D.num <> N.num)
	AND (A.num <> C.num)
	AND (A.num <> H.num)
	AND (C.num <> H.num)
	AND (D.num <> H.num)
	AND (A.num <> I.num)
	AND (C.num <> I.num)
	AND (H.num <> I.num)
	AND (D.num <> I.num)
	AND (A.num <> L.num)
	AND (C.num <> L.num)
	AND (H.num <> L.num)
	AND (I.num <> L.num)
	AND (D.num <> L.num)
	AND (A.num <> D.num)
	AND (C.num <> D.num)
      これを加えて 
      
SELECT
	(100 * M.num + 10 * A.num + N.num) AS MAN,
	'+' AS [PLUS],
	(10000 * W.num + 1000 * O.num + 100 * M.num + 10 * A.num + N.num) AS WOMAN,
	'=' AS [EQUAL],
	(10000 * C.num + 1000 * H.num + 100 * I.num + 10 * L.num + D.num) AS CHILD
FROM
	NumList AS A,
	NumList AS C,
	NumList AS D,
	NumList AS H,
	NumList AS I,
	NumList AS L,
	NumList AS M,
	NumList AS N,
	NumList AS O,
	NumList AS W
WHERE
	((100 * M.num + 10 * A.num + N.num)
	+ (10000 * W.num + 1000 * O.num + 100 * M.num + 10 * A.num + N.num)
	= (10000 * C.num + 1000 * H.num + 100 * I.num + 10 * L.num + D.num))
	AND (M.num>=1) AND (W.num >= 1) AND (C.num>=1)
	AND (O.num <> W.num)
	AND (M.num <> W.num)
	AND (A.num <> W.num)
	AND (N.num <> W.num)
	AND (C.num <> W.num)
	AND (H.num <> W.num)
	AND (I.num <> W.num)
	AND (L.num <> W.num)
	AND (D.num <> W.num)
	AND (M.num <> O.num)
	AND (A.num <> O.num)
	AND (N.num <> O.num)
	AND (C.num <> O.num)
	AND (H.num <> O.num)
	AND (I.num <> O.num)
	AND (L.num <> O.num)
	AND (D.num <> O.num)
	AND (A.num <> M.num)
	AND (C.num <> M.num)
	AND (H.num <> M.num)
	AND (I.num <> M.num)
	AND (L.num <> M.num)
	AND (D.num <> M.num)
	AND (M.num <> N.num)
	AND (A.num <> N.num)
	AND (C.num <> N.num)
	AND (H.num <> N.num)
	AND (I.num <> N.num)
	AND (L.num <> N.num)
	AND (D.num <> N.num)
	AND (A.num <> C.num)
	AND (A.num <> H.num)
	AND (C.num <> H.num)
	AND (D.num <> H.num)
	AND (A.num <> I.num)
	AND (C.num <> I.num)
	AND (H.num <> I.num)
	AND (D.num <> I.num)
	AND (A.num <> L.num)
	AND (C.num <> L.num)
	AND (H.num <> L.num)
	AND (I.num <> L.num)
	AND (D.num <> L.num)
	AND (A.num <> D.num)
	AND (C.num <> D.num)
      さて実行!
      風呂にでも入ってこよう・・・。
      機械の性能にもよるが、暫くすると答えが出てくる。
      MANPLUSWOMANEQUALCHILD
      781+39781=40562
      856+39856=40712
      586+39586=40172
      という感じです。

  3. おわりに

    4. まぁ、SQL にまかせてループを書かない Prolog風(?)の使い方ということで。
    次はベクトル演算という視点でグラフ処理!・・・って次があるのか?

  4. おまけ

    5. ついでだから
    MIKO
    +MIKO
    NURSE
    をやってみたら 32通りの答えが出ました。
先頭へ
コメントはこちらにでも
NaruTo の 計算機譜表工房へ
NaruTo の御家へ