SQLについて教えてください。

下記のような2つのテーブルがあるとします。

このとき例えば「千葉」で検索した場合に、
table1の「b:c」を元に、table2から「川あり」と「谷あり」を取得し、
表示したいのですが、一つのSQL文で書く事は可能でしょうか?

可能でしたら、その文を教えてください。

table1

東京,a:b:c
千葉,b:c
埼玉,a

table2

a,山あり
b,川あり
c,谷あり

よろしくお願いします。

回答の条件
  • 1人2回まで
  • 登録:2007/12/11 18:20:42
  • 終了:2007/12/18 18:25:04

回答(3件)

id:chuken_kenkou No.1

chuken_kenkou回答回数722ベストアンサー獲得回数542007/12/11 19:06:00

ポイント27pt

できれば、RDBMS名とバージョンを明記してもらえると、適切なアドバイスができます。

「a:b:c」のようなコロン区切りやカンマ区切りでデータを持つことは、SQLの特性である柔軟な操作が

できなくなるので、別の行で管理することとします。

1.表定義例

create table table1
(pref        varchar(10),
 kwdid       char(1),
 primary key(pref,kwdid));

create table table2
(kwdid       char(1)  primary key,
 kwd         varchar(20));


2.テストデータの格納例

insert into table1 values('東京','a');
insert into table1 values('東京','b');
insert into table1 values('東京','c');
insert into table1 values('千葉','b');
insert into table1 values('千葉','c');
insert into table1 values('埼玉','a');

insert into table2 values('a','山あり');
insert into table2 values('b','川あり');
insert into table2 values('c','谷あり');


3.検索SQL例

select
  t1.kwdid,t2.kwd
 from table1 as t1
  inner join table2 as t2 
   on t1.kwdid=t2.kwdid
 where pref='千葉'
 order by t1.kwdid
;
id:worldtravel

ありがとうございます。

やはりそのようにしないとダメなのでしょうか?

できれば一つのテーブルで処理したいのですが...

無理ですか?

2007/12/11 19:16:13
id:Foolish_No1 No.2

Foolish_No1回答回数1ベストアンサー獲得回数02007/12/11 19:24:14

ポイント27pt

ORACLEでしか試していませんが

以下で取得はできました。

あまりお勧めはできませんが。。。

定義

CREATE TABLE table1(

COL1 varchar(10),

COL2 varchar(10),

primary key(COL1,COL2)

);

CREATE TABLE table2(

COL1 varchar(10),

COL2 varchar(10),

primary key(COL1,COL2)

);

SELECT

tb2.col2

FROM

TABLE1 tb1

,TABLE2 tb2

WHERE

tb1.COL1 = '千葉'

AND tb1.col2 LIKE ( '%' || tb2.COL1 || '%' )

id:worldtravel

ありがとうございます。

2007/12/11 20:20:33
id:chuken_kenkou No.3

chuken_kenkou回答回数722ベストアンサー獲得回数542007/12/11 19:47:42

ポイント26pt

まったくお勧めできませんが、仮に「a:c」といった情報を一列で管理する場合、どのような方法になるか

一例を提示しておきます。

1.表定義例

create table table1
(pref        varchar(10),
 kwdidlst    varchar(11),
 primary key(pref,kwdidlst));

create table table2
(kwdid       char(1)  primary key,
 kwd         varchar(20));

2.格納データ例

insert into table1 values('東京','a:b:c');
insert into table1 values('千葉','b:c');
insert into table1 values('埼玉','a');

insert into table2 values('a','山あり');
insert into table2 values('b','川あり');
insert into table2 values('c','谷あり');

3.検索例

select
  t2.kwdid,t2.kwd
 from table1 as t1
  inner join table2 as t2 
   on t1.kwdidlst like concat('%',kwdid,'%')
 where pref='千葉'
 order by t2.kwdid
;

性能上問題になるのは、t1.kwdidlst like concat('%',kwdid,'%')

部分で、MySQLはインデクスを有効利用しないため、母体件数に比例して性能が悪くなります。

id:worldtravel

コメント欄へもありがとうございました。

2007/12/11 20:21:08
  • id:kn1967
    回答権が無いのでヒントだけ。

    1対多
    LIKE

    LIKEの書き方はDBMSによって微妙に違うのでコメント欄にでも
    ORACLEとかSQLServerとかAccessとか使っているものを
    書いておくほうが良いと思いますよ。
  • id:worldtravel
    ありがとうございます。

    MySQL です。
  • id:chuken_kenkou
    =条件にできるのに、間違っても「県名 LIKE '%千葉%'」といった形式で条件指定はすべきではありません。
    多くのRDBMSで、前方一致以外ではインデクスがあっても有効利用できず、性能を出せません。
  • id:chuken_kenkou
    MySQLは、バージョン4.0以前、4.1、5.0以降で、大きな機能差があります。

    思いつくだけでも一例を示すと、以下のような機能が実装されています。

    MySQL 4.0以前・・・いくつかのジョイン、UNION等は実装されている。
    MySQL 4.1 ・・・FULL JOIN、UNION JOIN等を除く主要なジョインの完全実装、サブクエリ、インライン
              ビュー、UNICODE等の実装
    MySQL 5.0 ・・・ストアドプロシジャ、トリガ、ビュー等の実装

    質問する場合は、バージョン明記をお勧めします。
  • id:worldtravel
    すいません。
    MySQL 4.0.2x
    です。
  • id:kn1967
    正規化を怠ると今回のようにアクロバティックな手法を用いなければならなくなり
    アクロバティックな操作を繰り返すのは面倒なだけでなく
    間違いがあっても見つけ難いものとなりますので正規化は必須だったりします。

    ×:LIKEの書き方は
    ○:SQLの書き方は
    ちなみに LIKEで県名のほうは使わないです。
  • id:chuken_kenkou
    >やはりそのようにしないとダメなのでしょうか?
    >できれば一つのテーブルで処理したいのですが...
    >無理ですか?

    一つのテーブル?
    テーブルは、質問での提示で2個だし、SQLは希望通り1SQLにしていますが?

    「a:b:c」を、一個の列で管理したいということでしょうか?
    もしそうなら、区切りを見つけa~cを取り出したりするのに関数の駆使や冗長な検索条件が必要に
    なります。そういった関数では、仮にインデクスがあっても利用できなくなってしまいます。

    母体のデータ件数は、最大で何件くらいでしょうか?
    1000件未満といった行数なら、インデクスを使わなくてもいいかも知れませんが、母体件数に比例して
    性能が劣化し、性能を出せません。
  • id:worldtravel
    あっ すいません2つのテーブルです。
    失礼しました。

    やはり教えていただいたやり方にする方が良いのですね。

    母体数は40,000〜50,000件程度です。

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません