[過去質問の続き]CREATE TEMPORARY TABLEで、3テーブルをくっつけたい

質問

http://oshiete1.goo.ne.jp/kotaeru.php3?q=2256727 のさらに続きです。
#1、#2さんのご助言＆プログラム例を、自分の環境に適用したいのですが、
テーブル構造の例示の仕方が悪かったため、どうしても自分の環境に
置き換えて適用することができませんでした。

文末の３テーブルに対し、県IDが3500種類、この3500行の右に、
・県名
・去年のGDPを時系列に12か月分
・去年の人口　〃
・今年のGDP　〃
・今年の人口　〃
…といった順序でCREATE TEMPORARY TABLEしたいのですが、
CREATE TABLEして、データをSELECTしないことにはINSERTできないですよね？
このあたりの関係がどうしてもわからず、挫折しかけたのですが、
３テーブルをエキスポートする度に、Excelでくっつけるのが面倒なのと、
何よりご教示下さった#1、#2さんのご好意を台無しにしたくなく、挫折したくありません。
県ごとにSUMで集計などせず、プレーンなデータをHTMLorTXT出力したいのですが…

■地域マスター
国ID　国名 県ID　県名
―――――――――――
C001　日本　001　東京
C001　日本　002　埼玉
C001　日本　003　千葉
C002　米国　004　バージニ
C002　米国　005　ミネソタ
C002　米国　006　カリフォ
C003　中国　007　四川
C003　中国　008　広東
C003　中国　009　雲南

■去年テーブル　→2004年12月～2005年11月のGNPと人口を【収録済み】
県ID　　年月　GNP　人口
――――――――――――
001　200412　100　30
001　200501　101　30
001　200502　103　31
　　：（略）
001　200511　106　34
002　200412　202　60

■今年テーブル　→2006年3月～2007年2月のGNPと人口を【収録中】
県ID　　年月　GNP　人口
――――――――――――
001　200603　108　40
001　200604　109　44
　　：（略）
001　200702　NULL NULL
002　200603　230　50

回答No.7

yambejp · ベストアンサー

あ、肝心のTABLE2作成のクエリーがぬけてましたね？
修正してソースをしたにつけます。

>(1) 人口とGNPは、それぞれdecimal(8,1)、decimal(7,2)と型が異なるようです。
>両者を`DATA` DOUBLE　とくくるのは、大丈夫でしょうか。

精度の問題であれば大丈夫だと思います。
表示の問題であれば、集計後は人口とGNPを同じ列で
表示すると考えると、桁数の違うdecimalは無効です。
当座DOUBLEが妥当でしょう。

>(2) これって、テンポラリテーブルでなく、普通のテーブルのようですが、
>今年テーブルの人口、GNPデータは、各県の担当者が毎月（日々）インターネット
>経由で日々更新（追加）しています。
>TABLE1は、照会する時点での最新データと同期が取れなくなるような気がするの
>ですが、「CREATE TABLE `TABLE1`・・・」を実行する前に、いったんDROPして
>再度CREATEしなおすという運用になるのでしょうか。

運用方法を変えられるなら通常テーブルが望ましいですが
そうも行かないようなのでテンポラリで運用ください。
テンポラリならDROPする必要はありません。

以下ご指摘いただいた分を修正した全文です。
CREATE TEMPORARY TABLE `TABLE1`
(`県ID` VARCHAR(10)
,`年月` VARCHAR(6)
,`KIND` VARCHAR(10)
,`DATA` DOUBLE);
INSERT INTO `TABLE1`
SELECT `県ID`,`年月`,'GNP' AS `KIND`,`GNP` AS `DATA` FROM `去年テーブル` ;
INSERT INTO `TABLE1`
SELECT `県ID`,`年月`,'人口' AS `KIND`,`人口` AS `DATA` FROM `去年テーブル` ;
INSERT INTO `TABLE1`
SELECT `県ID`,`年月`,'GNP' AS `KIND`,`GNP` AS `DATA` FROM `今年テーブル` ;
INSERT INTO `TABLE1`
SELECT `県ID`,`年月`,'人口' AS `KIND`,`人口` AS `DATA` FROM `今年テーブル` ;

CREATE TEMPORARY TABLE `TEMP_PIVOD`(
`年月` VARCHAR(10)
,`FLG` VARCHAR(10)
,`1` INT DEFAULT 0
,`2` INT DEFAULT 0
,`3` INT DEFAULT 0
,`4` INT DEFAULT 0
,`5` INT DEFAULT 0
,`6` INT DEFAULT 0
,`7` INT DEFAULT 0
,`8` INT DEFAULT 0
,`9` INT DEFAULT 0
,`10` INT DEFAULT 0
,`11` INT DEFAULT 0
,`12` INT DEFAULT 0
);
INSERT INTO `TEMP_PIVOD`(`年月`,`FLG`,`1`) VALUES('200412','去年',1);
INSERT INTO `TEMP_PIVOD`(`年月`,`FLG`,`2`) VALUES('200501','去年',1);
INSERT INTO `TEMP_PIVOD`(`年月`,`FLG`,`3`) VALUES('200502','去年',1);
INSERT INTO `TEMP_PIVOD`(`年月`,`FLG`,`4`) VALUES('200503','去年',1);
INSERT INTO `TEMP_PIVOD`(`年月`,`FLG`,`5`) VALUES('200504','去年',1);
INSERT INTO `TEMP_PIVOD`(`年月`,`FLG`,`6`) VALUES('200505','去年',1);
INSERT INTO `TEMP_PIVOD`(`年月`,`FLG`,`7`) VALUES('200506','去年',1);
INSERT INTO `TEMP_PIVOD`(`年月`,`FLG`,`8`) VALUES('200507','去年',1);
INSERT INTO `TEMP_PIVOD`(`年月`,`FLG`,`9`) VALUES('200508','去年',1);
INSERT INTO `TEMP_PIVOD`(`年月`,`FLG`,`10`) VALUES('200509','去年',1);
INSERT INTO `TEMP_PIVOD`(`年月`,`FLG`,`11`) VALUES('200510','去年',1);
INSERT INTO `TEMP_PIVOD`(`年月`,`FLG`,`12`) VALUES('200511','去年',1);
INSERT INTO `TEMP_PIVOD`(`年月`,`FLG`,`1`) VALUES('200603','今年',1);
INSERT INTO `TEMP_PIVOD`(`年月`,`FLG`,`2`) VALUES('200604','今年',1);
INSERT INTO `TEMP_PIVOD`(`年月`,`FLG`,`3`) VALUES('200605','今年',1);
INSERT INTO `TEMP_PIVOD`(`年月`,`FLG`,`4`) VALUES('200606','今年',1);
INSERT INTO `TEMP_PIVOD`(`年月`,`FLG`,`5`) VALUES('200607','今年',1);
INSERT INTO `TEMP_PIVOD`(`年月`,`FLG`,`6`) VALUES('200608','今年',1);
INSERT INTO `TEMP_PIVOD`(`年月`,`FLG`,`7`) VALUES('200609','今年',1);
INSERT INTO `TEMP_PIVOD`(`年月`,`FLG`,`8`) VALUES('200610','今年',1);
INSERT INTO `TEMP_PIVOD`(`年月`,`FLG`,`9`) VALUES('200611','今年',1);
INSERT INTO `TEMP_PIVOD`(`年月`,`FLG`,`10`) VALUES('200612','今年',1);
INSERT INTO `TEMP_PIVOD`(`年月`,`FLG`,`11`) VALUES('200701','今年',1);
INSERT INTO `TEMP_PIVOD`(`年月`,`FLG`,`12`) VALUES('200702','今年',1);

CREATE TEMPORARY TABLE `TABLE2`
SELECT `県ID`,`KIND`,`FLG`
,SUM(`DATA`*`1`) AS `1`
,SUM(`DATA`*`2`) AS `2`
,SUM(`DATA`*`3`) AS `3`
,SUM(`DATA`*`4`) AS `4`
,SUM(`DATA`*`5`) AS `5`
,SUM(`DATA`*`6`) AS `6`
,SUM(`DATA`*`7`) AS `7`
,SUM(`DATA`*`8`) AS `8`
,SUM(`DATA`*`9`) AS `9`
,SUM(`DATA`*`10`) AS `10`
,SUM(`DATA`*`11`) AS `11`
,SUM(`DATA`*`12`) AS `12`
FROM `TABLE1`
LEFT JOIN `TEMP_PIVOD` ON `TEMP_PIVOD`.`年月`= `TABLE1`.`年月`
GROUP BY `県ID`,`FLG`,`KIND`;

CREATE TEMPORARY TABLE `FLG_TABLE`(`FLG` VARCHAR(10));
INSERT INTO `FLG_TABLE` VALUES('去年'),('今年');
CREATE TEMPORARY TABLE `KIND_TABLE`(`KIND` VARCHAR(10));
INSERT INTO `KIND_TABLE` VALUES('GNP'),('人口');

SELECT * FROM `地域マスター`,`FLG_TABLE`,`KIND_TABLE`
LEFT JOIN `TABLE2`
ON `地域マスター`.`県ID`=`TABLE2`.`県ID`
AND `FLG_TABLE`.`FLG`=`TABLE2`.`FLG`
AND `KIND_TABLE`.`KIND`=`TABLE2`.`KIND`
ORDER BY `地域マスター`.`県ID`,`FLG_TABLE`.`FLG`,`KIND_TABLE`.`KIND`

お礼

ようやくうまくできました。

やっぱりデータが多すぎるのがクエリー結果を返してこない原因だったようで、
ORDER BY句の前にWHERE 国ID='C001'などと絞込条件を挟んだらすぐに結果が出てきました。

あとは、php側で工夫すれば、期待していたような仕組みを作ることができそうです。
yamabejpさん、本当にお世話になりました。
今後とも、よろしくお願い致します。

補足

yamabejpさん、追加ご教示本当にありがとうございます。
今朝から、#7でご提示いただいた各SQLを本番環境に置き換え、
実行させていただいています。

とりあえず、phpMyAdmin上に#7にご提示いただいたSQL文を放り込んで
みたのですが、サーバー考え込んだまま問い合わせ結果が出力されてきませんでした。

とりあえず、5つあるCREATE文から「TEMPORARY」をとって一つ一つテーブルを作成して
みましたら、全て即刻クエリーが完了します。

結論、最後の「SELECT * FROM `地域マスター`,`FLG_TABLE`,`KIND_TABLE`・・・」
のところでつまづき、結果を出力しきれない（っぽい）ことがわかりました。

もしかしたら、SELECTの際に当方本番環境におけるWHERE条件がゆるいため、
無限ループみたいなことが起こってるのかもしれませんので原因について調査中です。

本当なら、「SELECTされましたー！ありがとうございました！！」といいたかった
ところですが、解決御礼まで今しばらくお待ちください。

なお、本番環境に適用（CREATE）した結果は文末の通りで、
#5、#6でご提示いただいたような結果にはなっていると思います。

取り急ぎ、深謝＆現状報告まで。

■TABLE1
県ID　年月　KIND　DATA
0001　200412　GNP　4348
0001　200501　GNP　3757
0001　200502　GNP　4361
0001　200503　GNP　6365
0001　200504　GNP　5245
0001　200505　GNP　4495
0001　200506　GNP　4294
0001　200507　GNP　5996

■TEMP_PIVOD
年月　FLG 1 2 3 4 5 6 7 8 9 10 11 12
200412 去年 1 0 0 0 0 0 0 0 0 0 0 0
200501 去年 0 1 0 0 0 0 0 0 0 0 0 0
200502 去年 0 0 1 0 0 0 0 0 0 0 0 0
200503 去年 0 0 0 1 0 0 0 0 0 0 0 0
200504 去年 0 0 0 0 1 0 0 0 0 0 0 0
200505 去年 0 0 0 0 0 1 0 0 0 0 0 0
200506 去年 0 0 0 0 0 0 1 0 0 0 0 0
200507 去年 0 0 0 0 0 0 0 1 0 0 0 0
200508 去年 0 0 0 0 0 0 0 0 1 0 0 0
200509 去年 0 0 0 0 0 0 0 0 0 1 0 0
200510 去年 0 0 0 0 0 0 0 0 0 0 1 0
200511 去年 0 0 0 0 0 0 0 0 0 0 0 1
200603 今年 1 0 0 0 0 0 0 0 0 0 0 0
200604 今年 0 1 0 0 0 0 0 0 0 0 0 0
200605 今年 0 0 1 0 0 0 0 0 0 0 0 0
200606 今年 0 0 0 1 0 0 0 0 0 0 0 0
200607 今年 0 0 0 0 1 0 0 0 0 0 0 0
200608 今年 0 0 0 0 0 1 0 0 0 0 0 0
200609 今年 0 0 0 0 0 0 1 0 0 0 0 0
200610 今年 0 0 0 0 0 0 0 1 0 0 0 0
200611 今年 0 0 0 0 0 0 0 0 1 0 0 0
200612 今年 0 0 0 0 0 0 0 0 0 1 0 0
200701 今年 0 0 0 0 0 0 0 0 0 0 1 0
200702 今年 0 0 0 0 0 0 0 0 0 0 0 1

■TABLE2
県ID KIND FLG 1 2 3 4 5 6 7 8 9 10 11 12
0001 GNP 今年 5598 4953 5018 0 0 0 0 0 0 0 0 0
0001 人口 今年 720 617 611 0 0 0 0 0 0 0 0 0
0001 GNP 去年 5069 4810 4832 5287 5108 4497 5323 5201 5457 4831 5769 5069
0001 人口 去年 648 619 617 667 646 587 693 671 709 624 747 680

■FLG_TABLE
FLG
去年
今年

■KIND TABLE
KIND
GNP
人口