LEFT JOINを多用するのはよくないですか？

質問

PHP4+MySQL4.0

http://oshiete1.goo.ne.jp/kotaeru.php3?q=2085277
の続きなのですが、同質問にご回答下さったsaintandreさんのSQL例文を
拡張して、下記のように2年分（24か月＝24行）をLEFT JOINしようとしたら、
phpMyAdmin上でSQL文を発行しても結果がなかなか返ってこなかったり
PHP4で簡単なHTMLリストを書き出すプログラムをIE6上で実行しても
途中で「ページがみつかりません」になったりで、結果が得られないことの方が
多いです。SQL文１本で強引に解決することはあきらめたほうがよいでしょうか。

画期的な解決策がもしあればご教示ください。

SELECT
`社員マスター`.`社員ID` AS `名前ID`
,`社員マスター`.`名前` AS `名前`
,`社員マスター`.`性別` AS `性別`
,`出席日数200601`.`出席日数` AS `出席日数200601`
,`出席日数200602`.`出席日数` AS `出席日数200602`
　　　：
　（…途中20か月分・略…）
　　　：
,`出席日数200611`.`出席日数` AS `出席日数200611`
,`出席日数200712`.`出席日数` AS `出席日数200712`
FROM `社員マスター`
LEFT JOIN `出席テーブル` AS `出席日数200601` ON `社員マスター`.`社員ID` = `出席日数200601`.`社員ID` AND `出席日数200601`.`年月` = '200601'
LEFT JOIN `出席テーブル` AS `出席日数200602` ON `社員マスター`.`社員ID` = `出席日数200602`.`社員ID` AND `出席日数200602`.`年月` = '200602'
　　　：
　（…途中20か月分・略…）
　　　：
LEFT JOIN `出席テーブル` AS `出席日数200711` ON `社員マスター`.`社員ID` = `出席日数200711`.`社員ID` AND `出席日数200711`.`年月` = '200711'
LEFT JOIN `出席テーブル` AS `出席日数200712` ON `社員マスター`.`社員ID` = `出席日数200712`.`社員ID` AND `出席日数200712`.`年月` = '200712'

回答No.3

yambejp · ベストアンサー

こんな感じでわかりますか？

<?PHP
$link=mysql_connect('host','user','pass');
mysql_select_db('db',$link);

$max=24;

for($i=0;$i<$max;$i++){
$m[]=date("Ym",mktime(0,0,0,1+$i,1,2006));
}

$sql="CREATE TEMPORARY TABLE `TEMP_PIVOD` (`年月` VARCHAR(6),";
foreach ($m as $key=>$val){
$sql.=($key==0?"":",")."`$val` INT";
}
$sql.=");";
$res=mysql_query($sql,$link);

$sql="INSERT INTO `TEMP_PIVOD` VALUES";
foreach ($m as $key=>$val){
$sql.=($key==0)?"":",";
$sql.="('$val'";
for($i=0;$i<$max;$i++){
$sql.=",".($key==$i?"1":"0");
}
$sql.=")";
}
$res=mysql_query($sql,$link);

$sql=<<<eof
SELECT `社員マスター`.`社員ID` AS `名前ID`
,`社員マスター`.`名前` AS `名前`
,`社員マスター`.`性別` AS `性別`
eof;
foreach ($m as $key=>$val){
$sql.=",SUM(`$val`*`出席日数`) AS `出席日数{$val}`";
}
$sql.=<<<eof
FROM `社員マスター`
INNER JOIN `出席テーブル` ON `社員マスター`.`社員ID`=`出席テーブル`.`社員ID`
INNER JOIN `TEMP_PIVOD` ON `TEMP_PIVOD`.`年月`=`出席テーブル`.`年月`
GROUP BY `名前`
ORDER BY `名前ID`
eof;
$res=mysql_query($sql,$link);

print "<table border>";
while($rows = mysql_fetch_array($res,MYSQL_ASSOC)){
print "<tr>";
foreach ($rows as $key=>$val){
print "<td>$val</td>";
}
print "</tr>";
}
print "</table>";

?>

お礼

yamabejpさん、いつもプログラムによるご提示、本当にありがとうございます。
また、（毎度のことながら）御礼が遅くなって大変失礼しました。

今日、ようやくじっくり取り組む時間が得られたのですが
実はforeachとか非常に苦手としているところで、
ご提示のSQLもcreate文、insert文、select文と３段構えなので
一つ一つPRINTしてようやく構造がつかめてきたところです。

＃２さんのSQLも参考にしながら、yamabejpさんのものを
本番に適用している最中ですが、またわからないことがあったら是非ご教示
よろしくお願い致します。

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

回答No.2

chukenkenkou

ジョインするのは、社員IDから名前を変換する1回だけでいいですね。

＝＝＝＝＝以下、テスト用の表定義＆データ＝＝＝＝＝
create table 社員マスター
(社員ID int primary key,
名前 varchar(8),
性別 tinyint);

insert into 社員マスター values
(1,'鈴木',1),
(2,'佐藤',1),
(3,'山本',2);

create table 出席テーブル
(社員ID int,
年月 char(6),
出席日数 smallint,
primary key(社員ID,年月));

insert into 出席テーブル values
(1,'200601',3),
(1,'200602',4),
(1,'200603',5),
(2,'200601',10),
(2,'200602',11),
(2,'200603',12),
(3,'200601',7),
(3,'200602',8),
(3,'200603',9);
＝＝＝＝＝ここまで＝＝＝＝＝

MySQL 4.1以降なら、次のように書け簡単なのですが。。。

select
x.社員ID AS 名前ID,
名前,
性別,
出席日数200601,
出席日数200602,
出席日数200603
from 社員マスター as x,
(
select
社員ID,
ifnull(sum(case 年月 when '200601' then 出席日数 end),0) as 出席日数200601,
ifnull(sum(case 年月 when '200602' then 出席日数 end),0) as 出席日数200602,
ifnull(sum(case 年月 when '200603' then 出席日数 end),0) as 出席日数200603
from 出席テーブル
group by 社員ID
) as y
where x.社員ID=y.社員ID
;

MySQL 4.0とのことなので、出席テーブルの検索結果を一時表に格納して利用する方法を示します。

create temporary table if not exists w出席テーブル
(社員ID int,
出席日数200601 smallint,
出席日数200602 smallint,
出席日数200603 smallint);

truncate table w出席テーブル;

insert into w出席テーブル
select
社員ID,
ifnull(sum(case 年月 when '200601' then 出席日数 end),0),
ifnull(sum(case 年月 when '200602' then 出席日数 end),0),
ifnull(sum(case 年月 when '200603' then 出席日数 end),0)
from 出席テーブル
group by 社員ID;

select
x.社員ID AS 名前ID,
名前,
性別,
出席日数200601,
出席日数200602,
出席日数200603
from 社員マスター as x,w出席テーブル as y
where x.社員ID=y.社員ID
order by x.社員ID
;

お礼

chukenkenkouさん、整然としたSQLのご提示ありがとうございました。

別の仕事からようやく本件に戻ってきました。

とりあえず、ご提示のTEMPテーブルで解決する方法を検討しているところです。

またわからないことがあれば、是非ご教示ください。

ちなみに、わたしもMySQL 4.1以降にして欲しかったのですが、
システム開発当初にphpmyadminとの日本語の扱いの親和性などの
問題にバージョンダウンにせざるをえなかったのだそうです（開発者・談）
古いものだといろいろ不便ですね。。。

回答No.1

yambejp

その手のケースはピボッドテーブルをご利用になると
効果があるかと思います。
あらかじめ用意しておいてもいいですし、テンポラリーで
つどつど作成するのでもかまいません。
実例ですと以下の感じになります。

CREATE TABLE `社員マスター` (`社員ID` VARCHAR(4) PRIMARY KEY,`名前` VARCHAR(30),`性別` INT);
INSERT INTO `社員マスター` VALUES('0001','田中一郎',1),('0002','鈴木太郎',1),('0003','佐藤花子',2);

CREATE TABLE `出席テーブル`(`年月` VARCHAR(6),`社員ID` VARCHAR(4),`出席日数` INT);
INSERT INTO `出席テーブル` VALUES
('200601','0001',3)
,('200602','0001',5)
,('200603','0001',2)
,('200604','0001',6)
,('200605','0001',7)
,('200606','0001',1)
,('200601','0002',2)
,('200602','0002',4)
,('200604','0002',7)
,('200606','0002',2)
,('200602','0003',3)
,('200603','0003',3)
,('200604','0003',2)
,('200605','0003',1)
,('200606','0003',1);

CREATE TEMPORARY TABLE `TEMP_PIVOD` (`年月` VARCHAR(6),`200601` INT,`200602` INT,`200603` INT,`200604` INT,`200605` INT,`200606` INT);
INSERT INTO `TEMP_PIVOD` VALUES
('200601',1,0,0,0,0,0)
,('200602',0,1,0,0,0,0)
,('200603',0,0,1,0,0,0)
,('200604',0,0,0,1,0,0)
,('200605',0,0,0,0,1,0)
,('200606',0,0,0,0,0,1);

SELECT
`社員マスター`.`社員ID` AS `名前ID`
,`社員マスター`.`名前` AS `名前`
,`社員マスター`.`性別` AS `性別`
,SUM(`200601`*`出席日数`) AS `出席日数200601`
,SUM(`200602`*`出席日数`) AS `出席日数200602`
,SUM(`200603`*`出席日数`) AS `出席日数200603`
,SUM(`200604`*`出席日数`) AS `出席日数200604`
,SUM(`200605`*`出席日数`) AS `出席日数200605`
,SUM(`200606`*`出席日数`) AS `出席日数200606`
FROM `社員マスター`
INNER JOIN `出席テーブル` ON `社員マスター`.`社員ID`=`出席テーブル`.`社員ID`
INNER JOIN `TEMP_PIVOD` ON `TEMP_PIVOD`.`年月`=`出席テーブル`.`年月`
GROUP BY `名前`
ORDER BY `名前ID`

お礼

yamabejpさん、いつもわかりやすい実例でご教示いただきありがとうございます。

理想（最終目的）としては、いくつかのテーブルをExcelで加工しやすいように
くっつけることで（今までは各テーブル別にCSVをエクスポートして、Excel上で
くっつけてていた）CSVを出力するたびに同じ加工するのに辟易して今回の計画を
思いつきました。表のサイズとしては、60列×2500行程度の大きさとなります。

で、最終目標は、
(1) 検索フォーム　http://example.com/export.php　を開いて
(2) 検索条件を設定して（性別チェックボックスとか、社員ID○以上○以下とか）
(3) 検索ボタンを押下すると、list.php（1社員=1行のHTMLリスト）を吐き出すとか、
　　あるいはCSV出力ボタンを押下したなら、CSV保存ダイアログが出る、
　　そんなイメージです。

さて、ご教授のように都合のよい順序に整列加工し、フォーマットも整えた
テンポラリ・テーブルを設ければ（後は単純に「SELECT * FROM MYTABLE」だけで
HTMLリストやCSVを出力できそうだし）大変効率はよいだろうと思います。

示していただいた実例のSQL文（CREATE TABLE `社員マスター`.....から
ORDER BY `名前ID`　　.....まで）は、list.phpに仕込んでおく、そういう意味
でしょうか？