MySQL

Question

esecua

235

202もっと見る

100pt

コンピュータウェブ制作

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が同じもので３つのカテゴリが入っているものをはじめてSELECTするSQLをお願いします。

回答の条件

1人5回まで

登録：2007/04/10 08:12:14
終了：2007/04/13 14:55:21

※ 有料アンケート・ポイント付き質問機能は2023年2月28日に終了しました。

No.1

icchan00008552007/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 ='低学年向け')

ではどうでしょうか？

ありがとうございます。

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

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

2007/04/10 09:22:40

No.2

kurukuru-neko18441552007/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

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

2007/04/10 10:19:47

No.3

sato_hyper1602007/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

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

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

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

2007/04/10 11:31:23

No.4

kurukuru-neko18441552007/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 
でもよい

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

2007/04/11 11:21:54

kurukuru-neko 2007/04/10 09:35:18

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

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 ("絵本","学習","低学年向け");
esecua 2007/04/10 12:36:12

kurukuru-nekoさんありがとうございます。

一度検討してみたいと思います。
kurukuru-neko 2007/04/11 00:36:50

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')
kurukuru-neko 2007/04/11 01:14:42

たびたびすいません

bookcateにindexを作成していなくても
データ量が多いと多少は早くなります。
kurukuru-neko 2007/04/11 04:40:32

>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;
とかすれば使えるようです。
esecua 2007/04/11 11:21:21

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

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

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

人力検索はてな MySQL すでに　http://q.hatena.ne.jp/1176.. 2007-04-10 16:14:55

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

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

リクエスト送信済

回答リクエストを送信したユーザーはいません

kurukuru-neko · Accepted Answer · 2007-04-11T12:10:48+09:00

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

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

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

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

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

なので１０進数で書くと

when "絵本" then 1

when "学習" then 2

when "低ｘｘ" then 4

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

（２倍するのは、２進数で桁上げをさせる為）

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

ありません。

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

となり０となります。

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

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

全部一致する場合は

7 = 1 + 2 + 4

（２進数では１１１）

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

です。

　１を２進数で表現すると　　１

　２を２進数で表現すると　１０

　４を２進数で表現すると１００

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

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

計算します。

論理は

0と0 -> 0

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

となります。

上記１，１０、１００を各桁で

論理和すると

　　    １
　 　 １０
　　１００
 ＝＝＝＝＝
　　１１１

となります。

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

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

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

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

kurukuru-neko · Accepted Answer · 2007-04-11T12:10:48+09:00

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

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

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

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

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

なので１０進数で書くと

when "絵本" then 1

when "学習" then 2

when "低ｘｘ" then 4

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

（２倍するのは、２進数で桁上げをさせる為）

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

ありません。

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

となり０となります。

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

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

全部一致する場合は

7 = 1 + 2 + 4

（２進数では１１１）

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

です。

　１を２進数で表現すると　　１

　２を２進数で表現すると　１０

　４を２進数で表現すると１００

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

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

計算します。

論理は

0と0 -> 0

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

となります。

上記１，１０、１００を各桁で

論理和すると

　　    １
　 　 １０
　　１００
 ＝＝＝＝＝
　　１１１

となります。

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

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

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

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

MySQL

ベストアンサー

kurukuru-neko18441552007/04/11 12:10:48

その他の回答（4件）

icchan00008552007/04/10 09:16:17

kurukuru-neko18441552007/04/10 09:30:31

sato_hyper1602007/04/10 11:12:11

kurukuru-neko18441552007/04/11 00:16:36

kurukuru-neko18441552007/04/11 12:10:48ここでベストアンサー

コメント（7件)

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

トラックバック