mysqlで文字列が一致した数を求め並べ替えたい

質問

name　| keyword　　
---------------------
イチゴ |　果物,赤,甘い,春
レモン |　果物,黄色,酸っぱい,種あり,柑橘類
ミカン |　果物,橙,甘い,酸っぱい,柑橘類,冬,こたつ
ブドウ |　果物,紫,甘い,皮あり,種あり,ワイン
人参　 | 野菜,橙,固い
大根 |　野菜,白,おでん,冬

このように、キーワードをコンマで分けているテーブルがあるとします。
例えば「果物」「甘い」「種あり」の三つのワードでkeywordを検索した時

name
-------
ブドウ　　（3ワード一致）
イチゴ　　(2ワード一致)
ミカン　　(2ワード一致)
レモン　　(1ワード一致)

上のように一致するキーワードが多い順に表示させたいのですがうまくいきません

select name from tb where keyword like '%果物% or keyword like '%甘い% or keyword like '%種あり% order by count(name);
これだと上手くいきませんが、意味的にはこのようにしたいと思ってます。
joinで同じテーブルを結合しても思うように行きませんでした。

何か良い方法があればご教授お願いいたします。

回答No.3

yambejp · ベストアンサー

＞keyword01,keyword02....keyword10と、別のカラムで作成

これもあまり正しいとは言えません
こんな感じでどうでしょう？

create table tb (name varchar(30) not null,keyword varchar(30) not null,unique key(name,keyword));

insert into tb values('イチゴ','果物'),('イチゴ','赤'),('イチゴ','甘い'),('イチゴ','春')
,('レモン','果物'),('レモン','黄色'),('レモン','酸っぱい'),('レモン','種あり'),('レモン','柑橘類')
,('ミカン','果物'),('ミカン','橙'),('ミカン','甘い'),('ミカン','酸っぱい'),('ミカン','柑橘類'),('ミカン','冬'),('ミカン','こたつ')
,('ブドウ','果物'),('ブドウ','紫'),('ブドウ','甘い'),('ブドウ','皮あり'),('ブドウ','種あり'),('ブドウ','ワイン')
,('人参','野菜'),('人参','橙'),('人参','固い')
,('大根','野菜'),('大根','白'),('大根','おでん'),('大根','冬');

//ここまでがデータ作成、以下抽出

select name,count(*) as count
from tb
where keyword in ('果物','甘い','種あり')
group by name
order by count desc

お礼

おお！
ありがとうございます。
とりあえず教えて頂いた方法で使用したいと思います。
データが多くなって処理が遅くなったらまた何か考えます。

回答No.2

yambejp

SQLのやり方としては最悪の部類にはいりますが、できないことはありません。
ただしキーワードデータがちょっとまずいですね
たとえば「酸っぱい」と「甘酸っぱい」を仕分けることができません。
そのためにキーワードには最低限前後に「,」をつけます。

create table tb (name varchar(30),keyword varchar(255));
insert into tb values(
'イチゴ',',果物,赤,甘い,春,'),(
'レモン',',果物,黄色,酸っぱい,種あり,柑橘類,'),(
'ミカン',',果物,橙,甘い,酸っぱい,柑橘類,冬,こたつ,'),(
'ブドウ',',果物,紫,甘い,皮あり,種あり,ワイン,'),(
'人参',',野菜,橙,固い,'),(
'大根',',野菜,白,おでん,冬,');

この状態で以下のようにすればよいでしょう

select name,
(keyword like '%,果物,%')
+(keyword like '%,甘い,%')
+(keyword like '%,種あり,%') as k
from tb
order by k desc

#1で指摘がありますが、まずは正規化を学習するところからはじめないと
いつまでたってもまっとうなデータ管理ができませんよ

補足

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

実際に作成しようとしているシステムは、あるデータにそれぞれ10個のキーワードを設定し、他のデータに登録されている10個のキーワードとの一致数から関連度が高いデータを探り当てるというシステムです。（youtubeの関連動画みたいに表示させたいと思っています）
もともと10個のキーワードはkeyword01,keyword02....keyword10と、別のカラムで作成していたのですが、検索の際に発行するクエリ文がとてつもなく長くなってしまうので一つのカラムにまとめました。
しかしデータベースとしてふさわしくないようなので元に戻します。

今回教えて頂いたクエリ文で一応は思うような処理ができるのですが、
>SQLのやり方としては最悪の部類にはいりますが
と指摘を受けるように、あまり効率の良い処理ではないのでどうしようか困っています

もし何か良い方法がありましたら知恵を拝借できませんでしょうか

回答No.1

chukenkenkou

カンマ区切りなどでデータを持つのでなく、正規化しましょう。
今のようなデータの持ち方は、SQLの長所を消しているだけです。

お礼

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

mysqlに関しては入門書しか持っていなかったために正規化と言う言葉さえ知りませんでした。
調べてみたらとても参考になったので、もう少し勉強し正規化してみようと思います。
ありがとうございました。