複数テーブルの集計

質問

お世話になっています。

複数テーブルの集計がわからないので質問させてください。

テーブル３のItem1～4にはテーブル２のItemIDを登録します。
エリア毎の参加人数をcsvデータを出力させたいです。

テーブル３においてテーブル２のItemIDが４つあるので、一人に対して４行必要なのかと思いましたが、テーブルの変更ができません。

テーブルの結合まではできましたがその後ができません。
テーブルの変更ができませんので、結合するためにテーブル２の構造とデータが同じテーブルを他に３つ作成しています。

どうぞよろしくお願いします。

テーブル１
AreaID 　 AreaName
---------------------
1 北海道
2 東北
3 関東
4 北陸

テーブル２
ItemID ItemName
---------------------
1 自由形50
2 自由形100
3 背泳ぎ50
4 平泳ぎ50

テーブル３
ID AreaID　　　Name　　Item1　　　Item2　　Item3 Item4
---------------------------------------------------------
1　　　2　　　　鈴木　　　　2　　　　1　　　　4　　　　　3
2　　　3　　　　佐藤　　　　1　　　　null　　　3　　　　null
3　　　1　　　　田中　　　　1　　　　2　　　　null　　　null
4　　　2　　　　伊藤　　　　3　　　　null　　　2　　　　4　
5　　　3　　　　川村　　　 null　　　2　　　　3　　　　null

「集計結果」
　　　　　　参加人数　自由形50　自由形100　背泳ぎ50　平泳ぎ50
---------------------------------------------------------------
北海道　　　　1　　　　　1　　　　　1　　　　　0　　　　　0　
東北　　　　　2　　　　　1　　　　　2　　　　　2　　　　　2　　
関東　　　　　2　　　　　1　　　　　1　　　　　2　　　　　0　　
北陸　　　　　0　　　　　0　　　　　0　　　　　0　　　　　0

回答No.1

chukenkenkou · ベストアンサー

MySQLの質問をする場合は、バージョンを明記しましょう。せっかく具体的なSQLを提示しても、質問者さんの環境では実行できない可能性があります。

>テーブルの変更ができません

どう頑張っても性能を出せなかったり、今後の機能拡張などが困難になったりしますよ？

下記SQLは、MySQL 4.1以降なら動くと思います。
テーブル２は、未使用です。

【SQL例】
select
AreaName,
count(distinct ID) 参加人数,
count(case when Item=1 then 1 else null end) as 自由形50,
count(case when Item=2 then 1 else null end) as 自由形100,
count(case when Item=3 then 1 else null end) as 背泳ぎ50,
count(case when Item=4 then 1 else null end) as 平泳ぎ50
from t1 as x
left join
(
select ID,AreaID,1 as Item from t3 where Item1=1 or Item2=1 or Item3=1 or Item4=1
union all
select ID,AreaID,2 as Item from t3 where Item1=2 or Item2=2 or Item3=2 or Item4=2
union all
select ID,AreaID,3 as Item from t3 where Item1=3 or Item2=3 or Item3=3 or Item4=3
union all
select ID,AreaID,4 as Item from t3 where Item1=4 or Item2=4 or Item3=4 or Item4=4
) as y
on x.AreaID=y.AreaID
group by AreaName
order by x.AreaID
;

お礼

ご回答ありがとうございました。
教えていただいたSQLで実現できました！
テーブル２は使うものとばかり考えていました…

バージョンはMySQL4.1.18です。提示せずすみません。。
テーブルの変更は改めてお願いしてみます。

本当にありがとうございました。

補足

すみません。もうひとつだけ教えていただきたいことがあります。
PHPで開発しており、教えていただいたSQLを
$sql="select
AreaName,
count(distinct ID) 参加人数,
count(case when Item=1 then 1 else null end) as 自由形50,
count(case when Item=2 then 1 else null end) as 自由形100,
count(case when Item=3 then 1 else null end) as 背泳ぎ50,
count(case when Item=4 then 1 else null end) as 平泳ぎ50
from t1 as x
left join
(
select ID,AreaID,1 as Item from t3 where Item1=1 or Item2=1 or Item3=1 or Item4=1
union all
select ID,AreaID,2 as Item from t3 where Item1=2 or Item2=2 or Item3=2 or Item4=2
union all
select ID,AreaID,3 as Item from t3 where Item1=3 or Item2=3 or Item3=3 or Item4=3
union all
select ID,AreaID,4 as Item from t3 where Item1=4 or Item2=4 or Item3=4 or Item4=4
) as y
on x.AreaID=y.AreaID
group by AreaName
order by x.AreaID";
としました。
しかし、PHPMyAdmin上からSQL文は問題なく通り期待した結果が得られましたが、
PHPからは
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource というエラーがでます。

mysql_error()で確認したところ以下のようなエラーがでました。
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '参加人数, count(case when Item=1 then 1 else null end) as 自由形

参加人数を「''」で囲むなどしてみましたが見当違いでした…
お手数かけて申し訳ないのですが、ご教授ください。

回答No.4

chukenkenkou

#1回答者です。

確かに「as」を忘れていましたね。
ただ、「式 as 別名」の「as」は、多くのRDBMSで省略可能であり、MySQLでも同様に省略可能です。
→マニュアルのselect構文のところにも、明記されています。

ところで、「回答への補足」と「回答へのお礼」は、どちらが最新のステータスなのかが分からない（ここのシステムの仕組みが分かっていない）のですが、まだ、PHPでは実行できていないのでしょうか？

お礼

ご回答ありがとうございました。

MyAdminから試して問題がなかったのでお礼を投稿させていただき、
PHPからエラーがでたので補足を追加させていただきました(^^;

しかし、先ほどエラーが解決致しました！
参加人数などの列名を英語にしたところ大丈夫でした。
日本語がエラーになったのは、余計な文字などがあったかもしれないです。この原因は調べている最中です。

おかげさまで実現できました！本当にありがとうございました。

回答No.3

hrm_mmm

エラー表示のところの
＞syntax to use near '参加人数
'参加人数' の前に as が必要だと思いますけど、PHPMyAdminでエラーが出ないのもちょっと解りかねます。

お礼

ご回答いただきありがとうございます。

asがないことに気づきPHPからはつけさせていただきました。
MyAdminからはasがなくても大丈夫でした…
asをつけてもエラーがでたままです。

回答No.2

hrm_mmm

まず、table1とtable3から集計出来るとして、項目名は直書きするしかないかな
MySQL4.0.1では以下のように
select table1.AreaName as area,count(table3.ID) as '参加人数',
count(table3.Item1) as '自由形50',count(table3.Item2) as '自由形100',
count(table3.Item3) as '背泳ぎ50', count(table3.Item4) as '平泳ぎ50'
from table1 left join table3 on table1.AreaID = table3.AreaID
group by table1.AreaName;

または、phpとか他のプログラム言語との連携で項目名変換表示することになるのでは？