こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

複数InnerJoin時の処理落ち回避法

DB:Access97

30個のテーブルがあり、
それぞれ1000件のレコードがあります。

全テーブルを内部結合の上、データの抽出をしたいのですが、
Inner Joinで内部結合を行うとAccessの応答が無くなってしまいます。

5分~10分程度で、
30個のテーブルを内部結合の上データの抽出を
行えるようにするには、SQLをどのように変えれば良いのでしょうか。
アドバイスをお待ちしております。


*** SQL ***

Select
   Table01.製品コード
   , Table01.シリアルNO
   , Table01.基準値 as 製品性能01
   , IIF(Table02.最大値 LIKE "NULL" or Table02.最小値 LIKE "NULL" , 0 , (CINT(Table02.最大値) + CINT(Table02.最小値)) / 2) as 製品性能02
   , IIF(Table03.基準値 LIKE "NULL" , 0 , CDBL(CCur(Table03.基準値))) as 製品性能03
   , IIF(Table04.基準値 LIKE "NULL" , 0 , CINT(Table04.基準値)) as 製品性能04
   , IIF(Table05.最大値 LIKE "NULL" , IIF(Table05.基準値 LIKE "NULL" , 0 , CINT(Table05.基準値)) , (CINT(Table05.最大値) + CINT(Table05.最小値)) /2) as 製品性能05
          ・
          ・(以下Table30まで同様)
          ・
From
   ((Table01
   Inner Join
   Table02
   Table01.製品コード = Table02.製品コード
   And Table01.シリアルNO = Table02.シリアルNO)
   Inner Join
   Table03
   Table01.製品コード = Table03.製品コード
   And Table01.シリアルNO = Table03.シリアルNO)
   Inner Join
   Table04
   Table01.製品コード = Table04.製品コード
   And Table01.シリアルNO = Table04.シリアルNO)
          ・
          ・(以下Table30まで同様)
          ・

投稿日時 - 2007-02-07 11:51:32

QNo.2731322

すぐに回答ほしいです

質問者が選んだベストアンサー

主キーは(製品コード,シリアルNO,製品性能名)ですよね?

製品性能名によって、製品性能の計算が異なっているようなので、
まず、製品性能の計算を先に済ませ、作業用のテーブルに保存する。

INSERT INTO 作業用 ( 製品コード, シリアルNO, 製品性能名, 製品性能 )
SELECT 製品コード, シリアルNO, 製品性能名, 基準値 AS 製品性能
FROM システムA
WHERE システムA.製品性能名="製品性能01";
---------------------------------------------
INSERT INTO 作業用 ( 製品コード, シリアルNO, 製品性能名, 製品性能 )
SELECT 製品コード, シリアルNO, 製品性能名, IIf(最大値 Like "NULL" Or 最小値 Like "NULL",0,(CInt(最大値)+CInt(最小値))/2) AS 製品性能
FROM システムA
WHERE システムA.製品性能名="製品性能02";
---------------------------------------------
 ・
 ・
 ・


作業用のテーブルを元にクロス集計をする。

TRANSFORM Sum(作業用.製品性能) AS 製品性能の合計
SELECT 作業用.製品コード, 作業用.シリアルNO
FROM 作業用
GROUP BY 作業用.製品コード, 作業用.シリアルNO
PIVOT 作業用.製品性能名;

これでシステムBのテーブル構成になると思います。

補足:
Sumを使っていますが、合計ではありません。
(製品コード,シリアルNO,製品性能名)が主キーの場合、該当するデータは1件ずつしかないので。

投稿日時 - 2007-02-07 20:11:05

お礼

> 主キーは(製品コード,シリアルNO,製品性能名)ですよね?
仰るとおりです。
クロス集計は存じておりませんでした。
SQLはある程度分かっているつもりでしたが、奥が深いです。
ありがとうございました。

投稿日時 - 2007-03-16 19:27:32

ANo.2

このQ&Aは役に立ちましたか?

1人が「このQ&Aが役に立った」と投票しています

回答(3)

#1です。2点ばかり。

1.IIFを使用しない
UPDATE テーブル1 SET テーブル1.新基準値 = ([最大値]+[最小値])/2
WHERE (((テーブル1.製品性能名)="製品性能02"));
このようなクエリの方が楽だし早いと思います。
留意点
・作業テーブルを作らず、作業フィールド「新基準値」を利用しています
・丸め処理はしていません
・最大値、最小値の少なくとも一方がnullだと、計算をスキップします。全ての性能に関し計算後、まとめてnullを0で更新すればよいでしょうか?

以上で新基準値が全て埋まった後、クロス集計をすればよいのですが、クロス集計が一般的SQLで扱えるのか疑問です。その場合はピボットテーブルを自作する方法があります。

2.ピボットテーブル
この場合はフィールドとして
製品性能名(文字列)、製品性能01~30(整数値)
の31フィールド。レコードとして
製品性能01~30
の30レコード。対角線上に1それ以外に0をセットします。名称を仮に「ピボットテーブル」とします。イメージは下記

|製品性能名|製品性能01|製品性能02|製品性能03|
|---------+---------+----------+---------+---------+---・・・
|製品性能01    1|     0|    0| 
|製品性能02    0|     1|    0|
|製品性能03    0|     0|    1|

これを利用して製品性能03までを展開したSQL文は
SELECT テーブル1.製品コード, テーブル1.シリアルNO, Sum([テーブル1].[基準値]*[ピボットテーブル].[製品性能01]) AS 製品性能01値, Sum([テーブル1].[基準値]*[ピボットテーブル].[製品性能02]) AS 製品性能02値, Sum([テーブル1].[基準値]*[ピボットテーブル].[製品性能03]) AS 製品性能03値
FROM テーブル1 INNER JOIN ピボットテーブル ON テーブル1.製品性能名 = ピボットテーブル.製品性能名
GROUP BY テーブル1.製品コード, テーブル1.シリアルNO;

投稿日時 - 2007-02-08 09:36:18

お礼

こちらのやり方で無事希望通りのデータを作成することができました。
ありがとうございました。

投稿日時 - 2007-03-16 19:24:44

直感的にはDBの設計を変更した方が良いと思われますが、提示されたSQLから「やりたいこと」を追いかける根気がありません。テーブルの内容とやりたいことの概要を説明されてはいかがでしょうか。

投稿日時 - 2007-02-07 13:30:11

補足

ご指摘ありがとうございます。

本SQL作成の目的は、システム間のデータコンバート準備です。

システムA(コンバート元)のデータをシステムB(コンバート先)へ持って行きたいのですが、
コンバートするには、データの加工が必要です。

*** システムA(コンバート元)のテーブル構成 ***

|製品コード|シリアルNO|製品性能名|最大値  |基準値  |最小値  |
|---------+---------+----------+--------+--------+--------|
|    1234|      01|製品性能01|   NULL|   1200|   NULL|
|    1234|      02|製品性能01|   NULL|   1300|   NULL|
|    1234|      01|製品性能02|   NULL|   NULL|   NULL|
|    1234|      02|製品性能02|     80|     40|     30|
|    9876|      01|製品性能02|     90|     60|     30|
|    0864|      01|製品性能02|     90|     70|     30|
|    9876|      01|製品性能03|   NULL|   NULL|   NULL|
|    0864|      01|製品性能03|    9.12|    8.00|    6.89|
            ・
            ・
            ・
*** システムB(コンバート先)のテーブル構成 ***

|製品コード|シリアルNO|製品性能01|製品性能02|製品性能03|
|---------+---------+----------+---------+---------+---・・・(製品性能30まで同様)
|    1234|      01|    1200|      00|    0.00|
|    1234|      02|    1300|      55|    0.00|
|    9876|      01|      00|      60|    0.00|
|    0864|      01|      00|      60|    8.00|
            ・
            ・
            ・

やり方は、以下の3ステップです。
1.システムAのデータを製品性能名別に分ける。(完了済み)

  *** 製品性能名別テーブル構成 ***
   <Table01>
   |製品コード|シリアルNO|製品性能名|最大値  |基準値  |最小値  |
   |---------+---------+----------+--------+--------+--------|
   |    1234|      01|製品性能01|   NULL|    1200|   NULL|
   |    1234|      02|製品性能01|   NULL|    1300|   NULL|
   
   <Table02>
   |製品コード|シリアルNO|製品性能名|最大値  |基準値  |最小値  |
   |---------+---------+---------+--------+--------+--------|
   |    1234|      01|製品性能02|   NULL|   NULL|   NULL|
   |    1234|      02|製品性能02|     80|     40|     30|
   |    9876|      01|製品性能02|     90|     60|     30|
   |    0864|      01|製品性能02|     90|     70|     30|
                 ・
                 ・
                 ・

2.製品性能別のデータを連結する。(ここで本問題が発生)
  連結後のデータ構成は、上記「システムB(コンバート先)のテーブル構成」の通り。

3.2.の連結データをシステムBのテーブルへ挿入する。


他、不明点等ございましたら、ご指摘ください。
よろしくお願い致します。

投稿日時 - 2007-02-07 15:13:56

あなたにオススメの質問