mysql 検索方法

質問

現在、mysqlの検索方法で悩んでいます。一番スマートな方法を教えていただけたらありがたいです。

内容)巨人一軍と巨人二軍の各試合結果を格納しているテーブルがあります。ここから、2011年の巨人二軍の勝敗を集計する。

テールブル）
■チームテーブル(id,チーム名)
1,巨人一軍|2,巨人二軍|3,・・・とデータが入っている。尚、idはプライマリーキー
■試合結果テーブル(r_id,自チームid、相手チーム、自チーム得点、相手チーム得点、試合年月日）
1,1,阪神一軍,2,3,2011-05-01|2,1,阪神一軍,5,10,2011-06-01|3,2,広島二軍,1,0,2011-05-02|・・・とデータが入っている。尚r_idはプライマリーキー

このデータベースから2011年の巨人二軍の勝敗(○勝□敗）及び勝率（0.55等)をスマートに算出するには、どう行いますか？

mysql 5.0.75-0 ubuntu
Apache/2.2.11 (Ubuntu) PHP/5.2.6-3ubuntu4.6

回答No.2

yamada_g · ベストアンサー

スマートかは分かりませんが、SQLで全部計算するなら下記のような感じではどうでしょうか。

select
　 チーム名,勝数,負数 /* ,引分数 */ ,勝数 / (勝数 + 負数 /* + 引分数 */) 勝率
from
　(select
　 　a.チーム名
　　,count(case when b.自チーム得点 > b.相手チーム得点 then 1 else null end) 勝数
　　,count(case when b.自チーム得点 < b.相手チーム得点 then 1 else null end) 負数
　　--,count(case when b.自チーム得点 = b.相手チーム得点 then 1 else null end) 引分数
　from チームテーブル a
　　inner join 試合結果テーブル b
　　on a.id = b.自チームid
　　and b.試合年月日 like '2011%'
　where a.id = '2'
　group by a.チーム名);

引分は無いのであれば、その部分は無視してください。
試合年月日のデータ型が分からなかったので違うようであれば適当に修正してください。

お礼

回答ありがとうございます。だいぶやりたいことに近い結果が得られました。
ちなみに最後のセミコロンの前にas ○○をつけないとerror 1248がでるようです。

回答No.1

marutone

まずは以下のSQLを実行します。

SELECT
'1' AS 管理ID,
COUNT(*) AS 勝数
FROM
試合結果テーブル
WHERE
試合結果テーブル.自チームid = '2'
AND 試合結果テーブル.自チーム得点 > 試合結果テーブル.相手チーム得点
UNION
SELECT
'2' AS 管理ID,
COUNT(*) AS 負数
FROM
試合結果テーブル
WHERE
試合結果テーブル.自チームid = '2'
AND 試合結果テーブル.自チーム得点 < 試合結果テーブル.相手チーム得点

そしてこのデータを取得した後にPHP側で、

$勝率 = 勝数 / (勝数 + 負数);

とすれば良いと思います。

お礼

回答ありがとうございます。大変勉強になりました。