mysql+perl勉強中です。テーブル1個のときのデータの登録、削除、検索など簡単なことは大体できるようになってきました。テーブル2個のときも挑戦したいのですが、例えばゲームの総当たり戦の場合を作りたいと思います。

ブラウザで、Aさん、Bさん・・・の一覧がを表示させて、Aさんをクリックすると、試合日、対戦相手、点差、勝敗とまとめの成績、勝ち数、負け数、勝率を表示させたいと思います。
はじめ、出場者の情報table1(no,name,email)番号、名前、メールのテーブルと、出場者ごとの成績テーブルtableA,tableB,・・・と作成するのかと思いましたが、人数分の成績表作っていたら大変かと思いました。
出場者の情報テーブルと成績テーブルの2個ぐらいのテーブルで作れないでしょうか。2個でも多数でもよいのですがわかりやすい方法をお願いします。

また、下記の項目で、もし入り組んだ作業が必要な項目がございましたら、”テーブルの動きにかんすることだけ”簡単でかまいませんのでよろしくお願いします。
・テーブルの構成
・試合をしたときの動き
・新規に出場者を追加するときの動き
・削除するときの動き

回答の条件
  • URL必須
  • 1人3回まで
  • 登録:2006/04/24 19:12:30
  • 終了:2006/04/26 22:12:44

ベストアンサー

id:bonlife No.2

回答回数421ベストアンサー獲得回数752006/04/26 10:31:07

ポイント100pt

id:thrillseekerさんの回答と重複しますが、2つのテーブルで良いと思います。

ちょっと試してみました。

意外とややこしくなってしまいましたので、おそらくもっとシンプルな方法があると思います。

users

  • user_id (PRIMARY KEY)
  • user_name (NOT NULL)
  • email (NOT NULL)

games

  • game_id (PRIMARY KEY, auto_increment)
  • user_1 (NOT NULL, users.user_idを外部参照)
  • user_2 (NOT NULL, users.user_idを外部参照)
  • game_date
  • user_1_score
  • user_2_score
  • game_result (デフォルト'0')

※game_resultは、user_1が勝ったら'1'、user_2が勝ったら'2'とする

Aさん、Bさん...の一覧の表示は以下の通りです。

SELECT user_id, user_name
FROM   users;

Aさんの試合日、対戦相手、点差、勝敗は以下の通りです。

(Aさんのuser_idを'00000001'としております。)

(
SELECT g.game_date, u.user_name, g.user_1_score - g.user_2_score, "win"
FROM   users u, games g
WHERE  g.user_1 = '00000001'
AND    g.user_2 = u.user_id
AND    g.game_result = '1'
)
UNION
(
SELECT g.game_date, u.user_name, g.user_2_score - g.user_1_score, "win"
FROM   users u, games g
WHERE  g.user_1 = u.user_id
AND    g.user_2 = '00000001'
AND    g.game_result = '2'
)
UNION
(
SELECT g.game_date, u.user_name, g.user_1_score - g.user_2_score, "lose"
FROM   users u, games g
WHERE  g.user_1 = '00000001'
AND    g.user_2 = u.user_id
AND    g.game_result = '2'
)
UNION
(
SELECT g.game_date, u.user_name, g.user_2_score - g.user_1_score, "lose"
FROM   users u, games g
WHERE  g.user_1 = u.user_id
AND    g.user_2 = '00000001'
AND    g.game_result = '1'
)
UNION
(
SELECT COALESCE(g.game_date,'未定') , u.user_name, "-" , "-"
FROM   users u LEFT OUTER JOIN games g
ON     u.user_id = g.user_2
WHERE  g.user_1 = '00000001'
AND    g.game_result = '0'
)
UNION
(
SELECT COALESCE(g.game_date, '未定') , u.user_name, "-" , "-"
FROM   users u LEFT OUTER JOIN games g
ON     u.user_id = g.user_1
WHERE  g.user_2 = '00000001'
AND    g.game_result = '0'
)
ORDER BY 1
;

Aさんがuser_1にある場合で勝った試合、Aさんがuser_2にある場合で勝った試合、Aさんがuser_1にある場合で負けた試合、Aさんがuser_2にある場合で負けた試合、Aさんがuser_1にある場合で未実施の試合、Aさんがuser_2にある場合で未実施の試合をUNIONで結合しています。

試合日程があらかじめ決まっている場合は、下2つの部分でのCOALESCEは不要です。

引き分けがあるような場合、game_resultの区分値に新たに'3'(引分)を追加し、上記と同様に2つのSELECT文をUNIONで結合すれば良いはずです。

Aさんの勝ち数は以下のように求めます。

SELECT count(*)
FROM   users u, games g
WHERE  g.user_1 = '00000001'
AND    g.user_2 = u.user_id
AND    g.game_result = '1'
;
SELECT count(*)
FROM   users u, games g
WHERE  g.user_1 = u.user_id
AND    g.user_2 = '00000001'
AND    g.game_result = '2'
)

上記の2つの結果を足したものがAさんの勝ち数です。

負け数の取得も同様です。

勝率は、勝ち数、負け数からプログラム側の処理で算出すれば良いと思います。

試合結果の登録はgamesへのUPDATEで可能です。

ユーザの追加はusersへのINSERTで可能です。

この際、総当り戦にするのであれば、追加したユーザ情報を元にgamesへのINSERTをプログラム側で自動的に生成できるようにしておいた方が良いでしょう。

イメージとしては、追加したユーザ以外のuser_idのリストをusersから取得し、それを元にINSERT文を作ります。

参考になれば幸いです。

ここまで試してみて気づいたのですが、試合の事前情報を持つテーブルと試合結果を持つテーブルを分けた方がスッキリするかもしれません。

一度検討してみてください。

id:rain2003

コードまで書いていただいてありがとうございます。感動しました!

>試合の事前情報を持つテーブルと試合結果を

>持つテーブルを分けた方がスッキリするかも

いろいろと動かしながら検討させていただきたいと思います。

何かありましたらまたよろしくお願いします。

2006/04/26 22:08:10

その他の回答(1件)

id:thrillseeker No.1

thrillseeker回答回数328ベストアンサー獲得回数372006/04/26 01:43:04

ポイント25pt

私だったら、次の二つのテーブルで構成します。

A) 出場者テーブル (番号,名前,E-Mail)

B) 試合テーブル (試合番号,試合日,出場者1,出場者2,出場者1の得点,出場者2の得点,勝者)

で、出場者別の戦績を表示するには、表示のたびに以下のように動的に生成するようにします。

1) 試合テーブルから対象となる出場者が出場している試合を WHERE 句で指定して SELECT し、試合ごとに各カラムを結果として表示する。

2) 対象となる出場者が勝者となっている試合の数を試合テーブルからWHERE 句とCOUNT() 関数を組み合わせて集計→勝利数にする。

3) 対象となる出場者の対戦相手が勝者となっている試合の数を試合テーブルからWHERE 句とCOUNT() 関数を組み合わせて集計→敗戦数にする。

4) 必要に応じて、引き分け数を集計。

5) 2)〜4)の結果から勝率を perl で算出。

6) 2)〜5)の結果をまとめて表示。

出場者の追加及び試合結果の追加はその都度やって行けば、上記のように表示の際に最新の内容から毎回算出する方法の場合特に問題ないと思います。

ただし、出場者の削除は過去の試合結果の内容に不備を発生させるので避けた方が良いと思います。例えば出場者テーブルに「活動中」のようなカラムを作ってこの値を0にすることで区別すると良いかもしれません。

参考:http://homepage2.nifty.com/sak/w_sak3/doc/sysbrd/mysql_08.ht...

id:rain2003

大変丁寧にありがとうございます。動きもよくわかりました。

SQL勉強していて思ったのは、テーブルの構成ってとても大事なような気がしました。文法だけではなくて、いろいろな事例が載っているサイトや本を読んで作り方を修行したいと思います。

ところで、プログラム勉強し始めてから英語のページに行くのもそんなにいやではなくなりました。英語が得意というわけではなく、英語のほうがまだ理解できるので。英語のページになれることができればいろんなアイディア、ソースコード、フリーソフトに触れることができるので、プログラミング勉強するときに実は英語って大事なのかなぁと思いました。

2006/04/26 02:40:47
id:bonlife No.2

回答回数421ベストアンサー獲得回数752006/04/26 10:31:07ここでベストアンサー

ポイント100pt

id:thrillseekerさんの回答と重複しますが、2つのテーブルで良いと思います。

ちょっと試してみました。

意外とややこしくなってしまいましたので、おそらくもっとシンプルな方法があると思います。

users

  • user_id (PRIMARY KEY)
  • user_name (NOT NULL)
  • email (NOT NULL)

games

  • game_id (PRIMARY KEY, auto_increment)
  • user_1 (NOT NULL, users.user_idを外部参照)
  • user_2 (NOT NULL, users.user_idを外部参照)
  • game_date
  • user_1_score
  • user_2_score
  • game_result (デフォルト'0')

※game_resultは、user_1が勝ったら'1'、user_2が勝ったら'2'とする

Aさん、Bさん...の一覧の表示は以下の通りです。

SELECT user_id, user_name
FROM   users;

Aさんの試合日、対戦相手、点差、勝敗は以下の通りです。

(Aさんのuser_idを'00000001'としております。)

(
SELECT g.game_date, u.user_name, g.user_1_score - g.user_2_score, "win"
FROM   users u, games g
WHERE  g.user_1 = '00000001'
AND    g.user_2 = u.user_id
AND    g.game_result = '1'
)
UNION
(
SELECT g.game_date, u.user_name, g.user_2_score - g.user_1_score, "win"
FROM   users u, games g
WHERE  g.user_1 = u.user_id
AND    g.user_2 = '00000001'
AND    g.game_result = '2'
)
UNION
(
SELECT g.game_date, u.user_name, g.user_1_score - g.user_2_score, "lose"
FROM   users u, games g
WHERE  g.user_1 = '00000001'
AND    g.user_2 = u.user_id
AND    g.game_result = '2'
)
UNION
(
SELECT g.game_date, u.user_name, g.user_2_score - g.user_1_score, "lose"
FROM   users u, games g
WHERE  g.user_1 = u.user_id
AND    g.user_2 = '00000001'
AND    g.game_result = '1'
)
UNION
(
SELECT COALESCE(g.game_date,'未定') , u.user_name, "-" , "-"
FROM   users u LEFT OUTER JOIN games g
ON     u.user_id = g.user_2
WHERE  g.user_1 = '00000001'
AND    g.game_result = '0'
)
UNION
(
SELECT COALESCE(g.game_date, '未定') , u.user_name, "-" , "-"
FROM   users u LEFT OUTER JOIN games g
ON     u.user_id = g.user_1
WHERE  g.user_2 = '00000001'
AND    g.game_result = '0'
)
ORDER BY 1
;

Aさんがuser_1にある場合で勝った試合、Aさんがuser_2にある場合で勝った試合、Aさんがuser_1にある場合で負けた試合、Aさんがuser_2にある場合で負けた試合、Aさんがuser_1にある場合で未実施の試合、Aさんがuser_2にある場合で未実施の試合をUNIONで結合しています。

試合日程があらかじめ決まっている場合は、下2つの部分でのCOALESCEは不要です。

引き分けがあるような場合、game_resultの区分値に新たに'3'(引分)を追加し、上記と同様に2つのSELECT文をUNIONで結合すれば良いはずです。

Aさんの勝ち数は以下のように求めます。

SELECT count(*)
FROM   users u, games g
WHERE  g.user_1 = '00000001'
AND    g.user_2 = u.user_id
AND    g.game_result = '1'
;
SELECT count(*)
FROM   users u, games g
WHERE  g.user_1 = u.user_id
AND    g.user_2 = '00000001'
AND    g.game_result = '2'
)

上記の2つの結果を足したものがAさんの勝ち数です。

負け数の取得も同様です。

勝率は、勝ち数、負け数からプログラム側の処理で算出すれば良いと思います。

試合結果の登録はgamesへのUPDATEで可能です。

ユーザの追加はusersへのINSERTで可能です。

この際、総当り戦にするのであれば、追加したユーザ情報を元にgamesへのINSERTをプログラム側で自動的に生成できるようにしておいた方が良いでしょう。

イメージとしては、追加したユーザ以外のuser_idのリストをusersから取得し、それを元にINSERT文を作ります。

参考になれば幸いです。

ここまで試してみて気づいたのですが、試合の事前情報を持つテーブルと試合結果を持つテーブルを分けた方がスッキリするかもしれません。

一度検討してみてください。

id:rain2003

コードまで書いていただいてありがとうございます。感動しました!

>試合の事前情報を持つテーブルと試合結果を

>持つテーブルを分けた方がスッキリするかも

いろいろと動かしながら検討させていただきたいと思います。

何かありましたらまたよろしくお願いします。

2006/04/26 22:08:10

コメントはまだありません

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません