重要なお知らせ

「教えて! goo」は2025年9月17日(水)をもちまして、サービスを終了いたします。詳細はこちら>

電子書籍の厳選無料作品が豊富!

すいません、先ほど「条件が複雑なSQL文」の質問を立てさせてもらったのですが、
あとから自分で考えてもおかしな(どうやっていいのか分からないというか・・・)
部分がありましたので、今回はそれよりは簡潔(?^^;))な内容の質問なのですが、
例えば、「abcd」という文字列が、あるテーブルのnameに含まれてるかを調べたいとします。
そしてマッチした割合やあと何文字でマッチするか?のデータも欲しいのです。
この場合のSQL文はどのように記述すれば良いのでしょうか?

【調べる文字列】
abcd

[tbl]
id  name
1   ab
2   abcd
3   abcdefgh
4   xyz

【得たい結果(マッチした割合%の降順)】
[tblのid] [nameにマッチした割合%(パーセント)] [nameにあと何文字でマッチするか?]
  2          100                        0
  3           50                        4

A 回答 (8件)

SQLできました。



<?php
$str = "abc";
$len = strlen($str);

$whereStr = array();
$chunk = array();

for ( $i=0;$i<$len;$i++ ) {//開始位置
$chunk[] = substr($str, $i, 1);
}

$array = array_count_values($chunk);

foreach ( $array as $key=>$val ) {
$whereStr[] = "(SELECT `id` , `name`, {$val} as `match_count` FROM `tbl` WHERE `name` REGEXP '[{$key}]{{$val}}')";
}

$count = count($whereStr);
$where = implode(" UNION ALL ", $whereStr);

$sql = "SELECT
`id`
, `name`
, LENGTH(`name`)-SUM(`match_count`) as `rest`
, SUM(`match_count`) / LENGTH(`name`) * 100 as `percent`
FROM ({$where}) UT
GROUP BY `id`
HAVING SUM(`match_count`)=LENGTH('{$str}')
ORDER BY `id`";
//ご自身の環境に合わせてください
$cn = mysql_connect('localhost', 'root', 'password');

mysql_select_db('test', $cn);
$res = mysql_query($sql, $cn) or die(mysql_error());
$row = mysql_fetch_assoc($res);

do {
echo print_r($row, 1) . '<br />';

} while($row = mysql_fetch_assoc($res));

?>
    • good
    • 1
この回答へのお礼

ご回答ありがとうございます。
おお、まさしく理想とする結果が得られました。
ありがとうございます。

お礼日時:2011/10/28 23:38

ずっと、試行錯誤しているけど、難しいねこれ!


時間かかりそう。とりあえず、何日かは質問締めないで欲しいな。
    • good
    • 0

ごめんなさい、仕様を理解していなかった。


「ab」という文字群で評価をしていたので全然処理がちがいました。

一文字ずつ評価するならプロシージャで処理すればよさげですけど
それなりに面倒なので、せっかく別解がいいとこいっているようなので
そちらを応用されるとよいかと
    • good
    • 0

まあ、あとは好きにアレンジしてくださいな。

    • good
    • 0

これSQLだけで考えると難しいですね。

PHPのコードで書いてみましたので参考になればと思います。

$str = "ab";
$len = strlen($str);

$chunk = array();
$whereStr = array();

for ($k=$len; $k>=1; $k--) {
for ( $i=0;$i<$len-($k-1);$i++ ) {
$chunk = substr($str, $i, $k);
$whereStr[] = "(SELECT `id`, `name`, '{$chunk}' as `str` FROM `tbl` WHERE `name` LIKE '%{$chunk}%')";
}
}

$count = count($whereStr);
$where = implode(" UNION ALL ", $whereStr);

$sql = "SELECT
`id`
, `name`
, LENGTH(`name`) - LENGTH('{$str}') as `rest`
, LENGTH('{$str}') / LENGTH(`name`) * 100 as `percent`
FROM ({$where}) UT
WHERE LENGTH(`name`) - LENGTH('{$str}') >= 0
GROUP BY `id`";
mysql_query($sql, $cn);
    • good
    • 0
この回答へのお礼

ご回答ありがとうございます。
なるほど、最初「ab」で試してみたら希望通りの結果になったのですが、
次に「aa」で試したら

【調べる文字列】
aa

[tbl]
id  name
1   a
2   ab
3   abcd
4   cdefbgha
5   xyz

Array
(
[id] => 2
[name] => ab
[rest] => 0
[percent] => 100.0000
)
Array
(
[id] => 3
[name] => abcd
[rest] => 2
[percent] => 50.0000
)
Array
(
[id] => 4
[name] => cdefbgha
[rest] => 6
[percent] => 25.0000
)

という結果が得れたのですが、自分が求める結果としては「aa」なら
このname候補だと値を返さないという結果なのです。
最低でもnameには「a」が2文字以上あって始めて引っかかる(値を返す)
結果なのですが、そのようなことは可能なのでしょうか?

お礼日時:2011/10/27 21:58

どうもご利用の環境が2行以上のSQLが実行できないとか?


ちょっと工夫してみましたがどうでしょ?
#2の例をもとに・・・

//準備
create table tbl2(id int,name varchar(30));
insert into tbl2 values(1,'a'),(2,'ab'),(3,'abcd'),(4,'cdefbgha'),(5,'xyz');

//実行
select id
,length(@str) / length(name) *100 as hit
,length(name) - length(@str) as rest
from tbl
inner join (select @str:='ab' as str) as sub on
tbl.name regexp sub.str;

「@str:='ab'」の文字を変えてつかってください

この回答への補足

すいません、今気づいたのですが、仰ってることの意図としてはこちら(というか
そのままで)でしょうか?

select id
,length(@str) / length(name) *100 as hit
,length(name) - length(@str) as rest
from tbl
inner join (select @str:='ab' as str) as sub on
tbl.name regexp sub.str

ただこれでも全く同じ結果なのです・・・

補足日時:2011/10/27 22:04
    • good
    • 0
この回答へのお礼

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

>どうもご利用の環境が2行以上のSQLが実行できないとか?
PHPのZend Framework を使っているのですがそうかもしれないですね、、
ちょっと自分でも今のところ分からないのですが、、

なるほど、確認なのですが、次のようなSQLをセットして
試してみたのですが、これで合っていますでしょうか?

select id
,length('ab') / length(name) *100 as hit
,length(name) - length('ab') as rest
from tbl
inner join (select @str:='ab' as str) as sub on
tbl.name regexp sub.str

「ab」で試した結果なのですが、

【調べる文字列】
ab

[tbl]
id  name
1   a
2   ab
3   abcd
4   cdefbgha
5   xyz

Array
(
[id] => 2
[hit] => 100.0000
[rest] => 0
)
Array
(
[id] => 3
[hit] => 50.0000
[rest] => 2
)

となって、希望通りなら

Array
(
[id] => 4
[name] => cdefbgha
[rest] => 6
[percent] => 25.0000
)

が足りないのですが、なにか自分の記述間違いがあるのでしょうか?

お礼日時:2011/10/27 21:57

単純に考えていいならこんな感じ



set @str='abcd';
select id
,length(@str) / length(name) *100 as hit
,length(name) - length(@str) as rest
from tbl
where name like concat('%',@str,'%');


ちなみにabに対するababはヒット率50なのでしょうか?100なのでしょうか?
    • good
    • 0
この回答へのお礼

ご回答ありがとうございます。
大変申し訳ないですが、No.1の返答でも言いましたが、
質問自体にミスがあったので、No.1の返答の欄を参考に
考え直して頂けないのでしょうか?お願いします。

それと

set @str='abcd';
select id
,length(@str) / length(name) *100 as hit
,length(name) - length(@str) as rest
from tbl
where name like concat('%',@str,'%')

を実行したのですがエラーになってしまいます・・・

「Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error' in 」

このようなエラー情報しかないのですが、分かりますでしょか?
「concat()」の後ろの「;」が原因なのかと思って外してみたのですが、変わらないです。。
ちなみにtblの構造は

CREATE TABLE tbl (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(32)
) ENGINE=InnoDB;

お礼日時:2011/10/27 16:46

単純なLIKE検索では実装できないわけですね。


質問を質問で返すようで申し訳ないのですが、疑問点があるので。

疑問1
id 1 の場合、
1   50   -2
と返さなくて良いのですか?

疑問2
仮に、
id    name
5    ssdcbafg
というデータがあった場合は
5    100    4
となることを期待していますか?
    • good
    • 0
この回答へのお礼

ご回答ありがとうございます。
すいません、何度も大変申し訳ないですが、
今、No.2のyambejpさんの
>ちなみにabに対するababはヒット率50なのでしょうか?100なのでしょうか?
に対する返答を書いてて気が付いたのです、自分が質問した例は逆でした・・・
つまり【調べる文字列】が「ab」の場合でした・・・

【調べる文字列】
ab

[tbl]
id  name
1   a
2   ab
3   abcd
4   cdefbgha
5   xyz

【得たい結果(マッチした割合%の降順)】
[tblのid] [nameにマッチした割合%(パーセント)] [nameにあと何文字でマッチするか?]
  2          100                        0
  3           50                        2
  4           25                        6

もう一度質問を立て直したい気分なのですが、そうもいかないので
申し訳ないのですが、この例で考えて頂けないでしょうか?

>疑問1
なるほど、たしかに言われてみればそれもあった方がなにかといいのかなぁと
思いはじめた^^;)のですが、そうですね、とりあえず今のところは返さなくていいです。

みなさんから疑問が出てくるのは当然でしたね・・・
もう一度これでお願いします。。。

お礼日時:2011/10/27 16:45

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!