複数テーブルからのカウント集計

質問

初めて、投稿させて頂きます。

カルテテーブルに登録されたデータより、スタッフごとにどのサービスを何回対応したかを出力したいのですが、経験が浅く考え込んでいます。
また、日付の制限も指定したいです。
何卒、アドバイスをお願い致します。
MySQL 4.0.24-standardを使用しています。

カルテテーブル(chart_table)
---------------------------------------
staff | date | service1 | service2 | service3 | service4 |
1 2007-01-27 1 2 0 0
3 2007-02-07 0 0 3 4
2 2007-03-04 1 0 3 4

スタッフテーブル(staff_table)
---------------------------------------
no | id |
1 佐藤
2 鈴木
3 田中

サービステーブル(service_table)
---------------------------------------
no | name |
1 サービスA
2 サービスB
3 サービスC
4 サービスD

結果(2007-01 ～ 2007-03)
---------------------------------------
　　　　　　佐藤 | 鈴木 | 田中 |
サービスA 1 1 0
サービスB 1 0 0
サービスC 0 1 1
サービスD 0 1 1

回答No.1

yambejp · ベストアンサー

ちょっと難しいかもしれませんがピポットテーブルという考え方を
使うと解決します。

まず、chart_tableは今のままでは使い勝手が悪いのでこんな風にします。
staff | date |service
1　2007-01-27　1
1　2007-01-27　2
2　2007-03-04　1
2　2007-03-04　3
2　2007-03-04　4
3　2007-02-07　3
3　2007-02-07　4

ピボットテーブルというのは縦を横に入れ替える仕組みです。
こんな感じで左上から右下に1が入るテーブルを用意します。
id | 佐藤 | 鈴木 | 田中
1　1　0　0
2　0　1　0
3　0　0　1

以上を踏まえてざっと書きますので、可能ならそのまま実行してみてください。

データの作成
CREATE TABLE `chart_table`(
`staff` INT NOT NULL
,`date` DATE NOT NULL
,`service` INT NOT NULL
,UNIQUE(`staff`,`date`,`service`)
);
INSERT INTO `chart_table` VALUES
('1','2007-01-27','1')
,('1','2007-01-27','2')
,('3','2007-02-07','3')
,('3','2007-02-07','4')
,('2','2007-03-04','1')
,('2','2007-03-04','3')
,('2','2007-03-04','4');
CREATE TABLE `staff_table`(
`no` INT NOT NULL
,`id` VARCHAR(30)
,PRIMARY KEY(`no`)
);
INSERT INTO `staff_table` VALUES
('1','佐藤')
,('2','鈴木')
,('3','田中');
CREATE TABLE `service_table`(
`no` INT NOT NULL
,`name` VARCHAR(30)
,PRIMARY KEY(`no`)
);
INSERT INTO `service_table` VALUES
('1','サービスA')
,('2','サービスB')
,('3','サービスC')
,('4','サービスD');
CREATE TABLE `PIVOT`(
`id` INT NOT NULL
,`佐藤` INT
,`鈴木` INT
,`田中` INT
,PRIMARY KEY(`id`)
);
INSERT INTO `PIVOT` VALUES
(1,1,0,0)
,(2,0,1,0)
,(3,0,0,1);

集計用SQL
SELECT SE.`name`
,SUM(`佐藤`) AS `佐藤`
,SUM(`鈴木`) AS `鈴木`
,SUM(`田中`) AS `田中`
FROM `service_table` AS SE
LEFT JOIN `chart_table` AS CH
ON SE.`no`=CH.`service`
INNER JOIN `PIVOT` AS PI
ON CH.`staff`=PI.`id`
WHERE 1
AND CH.`date` BETWEEN '2007-01-01' AND '2007-03-31'
GROUP BY SE.`no`;

ここで、CH.`date` BETWEEN '始まりの日付' AND '終りの日付'
とすれば期間を調整することも可能です。

お礼

早いご解答ありがとうございます。
私には想像も付かない方法で勉強になります。
しかし、申し訳なのですが、従来テーブルで現状システムが稼働しており、集計の為に新たにテーブルを生成するのは、
仕様的に厳しいかと思いました。 (~~);
PHP上から、なんどかクエリを発行して集計するしかないでしょうか？

ご面倒をお掛け致しますが、他に良い方法がありましたら、
何卒、よろしくお願い致します。