MySQL


以下を実現するSQLをお願いします。

テーブル bookinfo

id auto_increment
select varchar(10)
booktitle text
bookid varchar(45)
bookcate varchar(20)

検索機能を作成しているのですが、本のカテゴリーが bookcate に "テクノロジー" や "English" のように入力されています。 ひとつの本でもカテゴリが複数ある場合があるので、その際には書くカテゴリごとにレコードを作製し入力し、bookidでまとめています。
たとえば絞込みで カテゴリが "絵本","学習","低学年向け" の本を検索したいのですが、

select * from bookinfo where select = "all" AND bookcate in ("絵本","学習","低学年向け") で検索すると、カテゴリに 絵本 が含まれるのもすべてがSELECTされてしまいます。

そうではなくて、bookidが同じもので3つのカテゴリが入っているものをはじめてSELECTするSQLをお願いします。



回答の条件
  • 1人5回まで
  • 登録:2007/04/10 08:12:14
  • 終了:2007/04/13 14:55:21

ベストアンサー

id:kurukuru-neko No.5

kurukuru-neko回答回数1844ベストアンサー獲得回数1552007/04/11 12:10:48

ポイント20pt

> when "絵本" then (1<<0)

> when "学習" then (1<<1)

> when "低学年向け" then (1<<2)

>この数字は検索カテゴリが増えるに従ってどのよう>につけていけばいいのでしょうか?

カテゴリーの数ではなく whenに書いた順番

なので10進数で書くと

when "絵本" then 1

when "学習" then 2

when "低xx" then 4

のように2倍した数字を順にかくだけです。

(2倍するのは、2進数で桁上げをさせる為)

カテゴリーを特定の数値に固定する必要は

ありません。

項目がwhenに一致しない場合はelseの0

となり0となります。

bitsの値は、各whenで一致した項目の

数値を足したものになります。

全部一致する場合は

7 = 1 + 2 + 4

(2進数では111)

bit_orは2進数で論理演算する機能

です。

 1を2進数で表現すると  1

 2を2進数で表現すると 10

 4を2進数で表現すると100

 のように順に0が増えます。

bit_orは上記内容の各桁の論理和を

計算します。

論理は

0と0 -> 0

1と0,0と1,1と1 -> 1

となります。

上記1,10、100を各桁で

論理和すると

      1
    10
  100
 =====
  111

となります。

カテゴリー64個以下で済むなら

はじめからカテゴリーを数値かして

保存しておいた方処理も早く出来る。

http://dev.mysql.com/doc/refman/4.1/en/group-by-functions.html

id:esecua

回答ありがとうございます。

早速試してみたいと思います。本当にありがとうございました。

2007/04/11 15:22:45

その他の回答(4件)

id:icchan0000 No.1

icchan0000回答回数85ベストアンサー獲得回数52007/04/10 09:16:17

ポイント10pt

select * from bookinfo

where bookid in (select bookid from bookinfo where bookcate ='絵本')

and bookid in (select bookid from bookinfo where bookcate ='学習')

and bookid in (select bookid from bookinfo where bookcate ='低学年向け')

ではどうでしょうか?

id:esecua

ありがとうございます。

確かにできるかもしれませんが、一回につき数個のSELECTをかけてしまっているので、負荷問題が心配です。

もっと効率のいい方法はないでしょうか?

2007/04/10 09:22:40
id:kurukuru-neko No.2

kurukuru-neko回答回数1844ベストアンサー獲得回数1552007/04/10 09:30:31

ポイント15pt

カラム名に予約語selectは使用出来ない

事はありませんがそのままでは使用できません。

必ず`select`と書く必要があります。

select * from bookinfo as b1

where `select`="all"

and bookcate in ("絵本","学習","低学年向け")

and ("絵本","学習","低学年向け")

=( (select bookcate from bookinfo as b2 where b2.bookid=b1.bookid and b2.bookcate="絵本" limit 1),

(select bookcate from bookinfo as b2 where b2.bookid=b1.bookid and b2.bookcate="学習" limit 1),

(select bookcate from bookinfo as b2 where b2.bookid=b1.bookid and b2.bookcate="低学年向け" limit 1)

)

group by bookid

id:esecua

あ、はい。わかりました。。。

2007/04/10 10:19:47
id:sato_hyper No.3

sato_hyper回答回数16ベストアンサー獲得回数02007/04/10 11:12:11

ポイント40pt

同じbookid内でbookcateが重複しないのであれば下記で抽出できると思います。

select * from bookinfo as b1

where

( select count(*) from bookinfo as b2 where b1.bookid = b2.bookid and

( b2.bookcate="絵本" or b2.bookcate="学習" or b2.bookcate="低学年向け")

) = 3

id:esecua

おぉ!まさにこんな感じのを求めていました。ありがとうございます。

もう少し効率のよい方法があるか待ってみます。

ありがとうございました。

2007/04/10 11:31:23
id:kurukuru-neko No.4

kurukuru-neko回答回数1844ベストアンサー獲得回数1552007/04/11 00:16:36

ポイント15pt

MYSQL 5.0以上が前提で少し反則気味ですが。

select booktitle,bookid,bit_or(
 case bookcate 
  when "絵本" then (1<<0) 
  when "学習" then (1<<1)
  when "低学年向け" then (1<<2) 
  else 0 
 end
) as bits 
from bookinfo group by bookid having bits=b'111';

又は
from bookinfo group by bookid having bit_count(bits)=3 
でもよい
id:esecua

大変貴重なご回答ありがとうございます。

2007/04/11 11:21:54
id:kurukuru-neko No.5

kurukuru-neko回答回数1844ベストアンサー獲得回数1552007/04/11 12:10:48ここでベストアンサー

ポイント20pt

> when "絵本" then (1<<0)

> when "学習" then (1<<1)

> when "低学年向け" then (1<<2)

>この数字は検索カテゴリが増えるに従ってどのよう>につけていけばいいのでしょうか?

カテゴリーの数ではなく whenに書いた順番

なので10進数で書くと

when "絵本" then 1

when "学習" then 2

when "低xx" then 4

のように2倍した数字を順にかくだけです。

(2倍するのは、2進数で桁上げをさせる為)

カテゴリーを特定の数値に固定する必要は

ありません。

項目がwhenに一致しない場合はelseの0

となり0となります。

bitsの値は、各whenで一致した項目の

数値を足したものになります。

全部一致する場合は

7 = 1 + 2 + 4

(2進数では111)

bit_orは2進数で論理演算する機能

です。

 1を2進数で表現すると  1

 2を2進数で表現すると 10

 4を2進数で表現すると100

 のように順に0が増えます。

bit_orは上記内容の各桁の論理和を

計算します。

論理は

0と0 -> 0

1と0,0と1,1と1 -> 1

となります。

上記1,10、100を各桁で

論理和すると

      1
    10
  100
 =====
  111

となります。

カテゴリー64個以下で済むなら

はじめからカテゴリーを数値かして

保存しておいた方処理も早く出来る。

http://dev.mysql.com/doc/refman/4.1/en/group-by-functions.html

id:esecua

回答ありがとうございます。

早速試してみたいと思います。本当にありがとうございました。

2007/04/11 15:22:45
  • id:kurukuru-neko
    >負荷問題が心配です。
    bookinfoのbookid,bookcateにindexを
    作成しないと全件検索の必要があります。

  • id:kurukuru-neko
    select b1.*,b2.bookcate,b3.bookcate from
    (select bookid,bookcate from bookinfo where `select`="all" and bookcate in ("絵本","学習","低学年向け")) as b1
    inner join bookinfo as b2 on b1.bookid=b2.bookid and b2.bookcate="絵本"
    inner join bookinfo as b3 on b1.bookid=b3.bookid and b3.bookcate="学習"
    inner join bookinfo as b4 on b1.bookid=b4.bookid and b4.bookcate="低学年向け"
    ;

    SubQueryが使えない/使わない場合
    select b1.* from
    bookinfo as b1
    inner join bookinfo as b2 on b1.bookid=b2.bookid and b2.bookcate="絵本"
    inner join bookinfo as b3 on b1.bookid=b3.bookid and b3.bookcate="学習"
    inner join bookinfo as b4 on b1.bookid=b4.bookid and b4.bookcate="低学年向け"
    where b1.`select`="all" and b1.bookcate in ("絵本","学習","低学年向け");
  • id:esecua
    kurukuru-nekoさんありがとうございます。

    一度検討してみたいと思います。
  • id:kurukuru-neko
    bookcateにindexを作成している場合は
    whereを指定するとさらに早くなる。

    from bookinfo where bookcate in ("絵本","学習","低学年向け") group by bookid having bits=b'111';

    又は
    from bookinfo where bookcate in ("絵本","学習","低学年向け") group by bookid having bit_count(bits)=3 でもよい

    回答にはwhereに`select` = "all"の条件は
    含めていません。

    一応解説:
     選択したい項目を二進数の数値でbit位置が
    重複しない2^nの数値を各項目に割当を行う。
    group byにより該当項目のbit和をすると
    選択されている項目を調べることが出来る。
    (数値はbigintタイプなので最大64項目まで
    =64bit)

    応用すればhavingで複雑な条件検索も可能。

    例えば項目に"自習"が追加である場合
    "絵本"+("学習" or "自習")+"低学年向け"
    のものを選択したい場合
    自習を (1<<3)
    とすると
    以下のようにかける
    ((bits & b'0101') =b'0101') and
    ((bits & b'1010')!=b'0000')




  • id:kurukuru-neko
    たびたびすいません

    bookcateにindexを作成していなくても
    データ量が多いと多少は早くなります。
  • id:kurukuru-neko
    >MYSQL 5.0以上に前提を下げる場合

    勘違いしてました。
    http://dev.mysql.com/doc/refman/4.1/en/control-flow-functions.html

    bit_orは3.23.56でも動いたので
    b'xxxx',bit_countを使わない方法なら
    使えますね。

    select booktitle,bookid,bit_or(
    case bookcate
    when "絵本" then (1<<0)
    when "学習" then (1<<1)
    when "低学年向け" then (1<<2)
    else 0
    end
    ) as bits
    from bookinfo group by bookid having bits=7;
    とかすれば使えるようです。

  • id:esecua
    おぉ!kurukuru-neko さん、ナイス情報です!ありがとうございます。

    ところで最後の bits=7の7はなんの数字になるんでしょうか?また、 then(1<<0)の1<<0や1<<2とありますが、この数字は検索カテゴリが増えるに従ってどのようにつけていけばいいのでしょうか?

    もしよろしければ解答欄からお答えください。

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

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

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

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