ちょっと難しいかもしれませんがピポットテーブルという考え方を
使うと解決します。
まず、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上から、なんどかクエリを発行して集計するしかないでしょうか？ ご面倒をお掛け致しますが、他に良い方法がありましたら、 何卒、よろしくお願い致します。