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

#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/b56259/adfns_regexp.htm

回答No.4

noname#212058

> 呼び出し元（php）で Aテーブルのカンマ区切りの値を
> 取得→再度その値を元にSQLを組んでBテーブルの
> 取得、という感じがいいということでしょうか？

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

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

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

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

回答No.3

root139

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-functions/listagg.html

回答No.2

bin-chan

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

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

お礼

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

回答No.1

noname#212058

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

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

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

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

お礼

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