select文の書き方

助けてください……(T-T)

oracleをつかっていますが、SQLが苦手すぎて、どうしたらいいか分かりません。。。
解決策をご存じの方、教えていただけないで しょうか？

テーブルは下記の2つがあります。

(1)Aテーブルの1カラムに、複数の商品IDを格 納している（カンマ区切り） (2)Bテーブルには、商品ID＆商品名の一覧が ある

このAテーブルの商品ID列に番号から、Bテー ブルの商品名を取得したいです。

■Aテーブル
no | 商品ID
1001 | 1、2
1002 | 1、2、3
1003 | 3

■Bテーブル
商品ID | 商品名
1 | 商品A
2 | 商品B
3 | 商品C

■とりたいデータの形
1001 | 商品A、商品B
1002 | 商品A、商品B、商品C
1003 | 商品C

カンマ区切りで格納しているとin句は使えな いと知りました。。

テーブルにカンマ区切りで格納することは、 変えることはできませんし、 SQLで1回で取得しなきゃいけないのです。

そんなこと、可能なのでしょうか。。

明日の朝までに教えていただければ、
すごくすごく助かります。。
すみませんが、宜しくお願いします。

No.5 回答者： root139 回答日時： 2014/02/23 23:38

#3 ですが、商品IDが2桁以上になるとダメですね。

マッチングに正規表現を使わなければならないかも知れません。

SELECT
no,
(SELECT LISTAGG(商品名, ', ') WITHIN GROUP (order by 商品ID) FROM Bテーブル b WHERE
REGEXP_LIKE(a.商品ID, '\s*(.*\D)?' || b.商品ID || '(\D.*)?\s*')
FROM Aテーブル a;

参考URL：http://docs.oracle.com/cd/E16338_01/appdev.112/b …

No.4 回答者： shitaba 回答日時： 2014/02/23 23:29

> 呼び出し元（php）で Aテーブルのカンマ区切りの値を

> 取得→再度その値を元にSQLを組んでBテーブルの
> 取得、という感じがいいということでしょうか？

一番単純な方法はそうですが、データ量によっては物凄く
遅くなります。『Ｂテーブルの取得』の回数は、なるべく少なく
なるように工夫したほうが良いでしょう。

Ａ案）
　Ｂテーブルのデータが十分少ない(300件くらい)場合：
　Ｂテーブルのデータを最初に全て取得して、PHP 側で
　『商品ID=>商品名』 の配列で持っておく。そして、Ａ
　テーブルの商品IDはこの配列を使って変換する。
　⇒ この方法なら、Bテーブルへのアクセスは１回だけ

Ｂ案)
　Ｂテーブルのデータが多いけど、実際に使われる商品の
　種類は少ない場合：
　Ｂテーブルから取得した結果を、『商品ID=>商品名』の
　配列に貯めていく。Ａテーブルの商品IDの変換は、まず
　配列で変換して、配列になければ Ｂテーブルから取得
　する。(Ｂテーブルから取ったデータは、次のデータのため
　に配列に入れておく)
　⇒ この方法なら、BテーブルへのアクセスはＡテーブルの
　　　商品の種類の数だけ

いずれもキャッシュ(配列)を使った、処理速度アップの常套
手段です。

No.3 回答者： root139 回答日時： 2014/02/23 23:18

Oracle 11g R2 以降で使える LISTAGG 関数を使えば出来るかも知れません。

かなり強引ですが・・・。
(それ以前のバージョンでも wmsys.wm_concat という同じ事をする隠し関数は有ったみたいです)

SELECT
no,
(SELECT LISTAGG(商品名, ', ') WITHIN GROUP (order by 商品ID) FROM Bテーブル b WHERE a.商品ID LIKE '%' || b.商品ID || '%') AS 商品名
FROM Aテーブル a;

なお、手元にOracleが無いので動作は未確認です。

参考URL：http://www.shift-the-oracle.com/sql/aggregate-fu …

No.2 回答者： bin-chan 回答日時： 2014/02/23 21:48

＃１さんのストアードプロシージャの敷居が高いなら。

ODBC経由でACCESSを用いて操作するとか。

この回答へのお礼

Accessでも連携して値を取得できるのですか！
勉強になります。
本当にありがとうございます！

お礼日時：2014/02/23 22:41

No.1 回答者： shitaba 回答日時： 2014/02/23 21:11

SQL１回で取るのは不可能です。

どうしてもDBへの呼出し１回で取る必要があるのであれば、
ストアドプロシージャを組むしかありません。

以下のような文字列切り出し関数を応用して作ることになり
ます。
http://www.shift-the-oracle.com/sql/functions/st …

ぶっちゃけ敷居が高いので、呼出側で処理できないか (複数
回の呼出しを許容できないか) 真面目に検討したほうが良い
です。

# 言っても詮無いことですが、このような DBの基本をガン無
# 視するようなデータの格納方法をするのがそもそもの間違
# いです。これで処理が簡単にできるわけがありません。
#
# 正直なことを言わせて頂くと、今後もデータを取得する SQL
# を作るたびに苦労するのは火を見るより明らかなので、カン
# マを使わないデータを入れるテーブルを別に作っておいた
# ほうが良いです。

この回答へのお礼

さっそく詳しくしくご回答をいただき、本当に感謝です！
やはり、無理ですか。。
調べてみても考えてみてもできる気がしなくて、でも確信できずに途方にくれてました
。
ストアドプロシージャでできるのですね！
調みてみます！
もし1回呼び出しにこだわらなければ、
呼び出し元（php）で
Aテーブルのカンマ区切りの値を取得→再度その値を元にSQLを組んでBテーブルの取得、という感じがいいということでしょうか？

お礼日時：2014/02/23 22:38