2文字以上の重なっている文字列を抜き出す方法

例えば、次のようなテーブルがあって、mojiretuカラムにおいて
他の行の文字列と2文字以上の重なっている部分の文字列を取得したいのです。
ただし条件があって下の場合だと、「ab」は1、2行目で重なっているのですが、
2行目の「ab」はそれで全ての（終結している）文字列なのでマッチさせないようにしたいです。
なので、このテーブルから取得したい文字列は
「cd」「bcd」「abc」「bc」「de」になります。できれば重なっている回数も含めて取得したい
（「cd」２回、「bcd」１回、「abc」１回、「bc」１回、「de」１回、）
のですが、どのようなSQLを記述すれば良いのでしょうか？

[テーブル]
mojiretuカラム

abcd
ab
cde
abcde
xy

No.4 回答者： yambejp 回答日時： 2011/10/04 23:04

ちなみに

SELECT * FROM T_TEMP;
で想定した文字を2文字以上に分割したデータ群はみえてますよね？

＞PHP側で$db->query("SELECT MOJIRETU,COUNT(*) -1 AS COUNT FROM T_TEMP GROUP BY
＞ MOJIRETU HAVING COUNT>0 AND MOJIRETU NOT IN(SELECT MOJIRETU FROM HOGE");

単にシンタックスエラーですね？
ケツにカッコ閉じがぬけてます

PHP側で$db->query("SELECT MOJIRETU,COUNT(*) -1 AS COUNT FROM T_TEMP GROUP BY MOJIRETU HAVING COUNT>0 AND MOJIRETU NOT IN(SELECT MOJIRETU FROM HOGE)");

この回答へのお礼

ご回答ありがとうございます。
なるほど、たしかに最後にある「;」を取り除こうとして
それまで取り除いていました・・・うっかりミスでした・・・すいません。
お陰様で理想的な結果を得ることができました。
ありがとうございます。

お礼日時：2011/10/05 00:20

No.3 回答者： yambejp 回答日時： 2011/10/04 22:10

あ、一点補足・・・・

INSERTの行はINGNOREにしないとエラーになる可能性があるので以下注意

INSERT INTO T_TEMP(NUM,MOJIRETU) VALUES(a,SUBSTR(b,I,J));
↓
INSERT IGNORE INTO T_TEMP(NUM,MOJIRETU) VALUES(a,SUBSTR(b,I,J));

この回答へのお礼

ご回答ありがとうございます。
＞INSERT IGNORE INTO T_TEMP(NUM,MOJIRETU) VALUES(a,SUBSTR(b,I,J));
IGNOREを追加してphpMyAdminでやってみたのですが、
同じようなエラー結果でした・・・
何が別の原因があのエラー文ではあるのでしょうか？

お礼日時：2011/10/04 22:49

No.2 ベストアンサー 回答者： yambejp 回答日時： 2011/10/04 15:55

もっと効率的な方法はあると思いますが、ざっくりとやってみました。

テーブル名などは順次自分の設定と置き換えてください。

(1)検索用のテーブルを設定しておきます。
※とりあえずテーブル名をHOGEとします。

CREATE TABLE HOGE (ID INT NOT NULL PRIMARY KEY,MOJIRETU VARCHAR(30));
INSERT INTO HOGE VALUES(1,'abcd'),(2,'ab'),(3,'cde'),(4,'abcde'),(5,'xy');

(2)テンポラリ用のテーブルをつくります
DROP TABLE IF EXISTS T_TEMP;
CREATE TABLE T_TEMP (
NUM INT,
MOJIRETU VARCHAR(20),
UNIQUE(NUM,MOJIRETU)
);

(3)プロシージャを作成します。
DROP PROCEDURE IF EXISTS INSERT_T_TEMP;
DELIMITER //
CREATE PROCEDURE INSERT_T_TEMP()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE I , J INT;
DECLARE a INT;
DECLARE b VARCHAR(30);
DECLARE CUR CURSOR FOR SELECT ID,MOJIRETU FROM HOGE;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
TRUNCATE T_TEMP;
OPEN CUR;
REPEAT
FETCH CUR INTO a, b;
IF NOT done THEN
SET J=2;
WHILE J<=LENGTH(b) DO
SET I=1;
WHILE I+J-1<=LENGTH(b) DO
INSERT INTO T_TEMP(NUM,MOJIRETU) VALUES(a,SUBSTR(b,I,J));
SET I=I+1;
END WHILE;
SET J=J+1;
END WHILE;
END IF;
UNTIL done END REPEAT;
CLOSE CUR;
END
//
DELIMITER ;

(4)プロシージャをコールします。
CALL INSERT_T_TEMP();

(5)SQLを発行します。
SELECT MOJIRETU,COUNT(*) -1 AS COUNT FROM T_TEMP GROUP BY MOJIRETU
HAVING COUNT>0 AND MOJIRETU NOT IN(SELECT MOJIRETU FROM HOGE);

この回答へのお礼

ご回答ありがとうございます。
試してみたのですが、次のようなエラーが出てしまいました・・・

＞Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1'

一応、下の文をphpMyadminからSQLで挿入して成功して、
PHP側で$db->query("SELECT MOJIRETU,COUNT(*) -1 AS COUNT FROM T_TEMP GROUP BY MOJIRETU HAVING COUNT>0 AND MOJIRETU NOT IN(SELECT MOJIRETU FROM HOGE");
を発行してみたのですが、、、これはどのようなエラーでまた対処方法を教えていただけないでしょうか？


DROP TABLE IF EXISTS HOGE;
CREATE TABLE HOGE (ID INT NOT NULL PRIMARY KEY,MOJIRETU VARCHAR(30));
INSERT INTO HOGE VALUES(1,'abcd'),(2,'ab'),(3,'cde'),(4,'abcde'),(5,'xy');

DROP TABLE IF EXISTS T_TEMP;
CREATE TABLE T_TEMP (
NUM INT,
MOJIRETU VARCHAR(20),
UNIQUE(NUM,MOJIRETU)
);

DROP PROCEDURE IF EXISTS INSERT_T_TEMP;
DELIMITER //
CREATE PROCEDURE INSERT_T_TEMP()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE I , J INT;
DECLARE a INT;
DECLARE b VARCHAR(30);
DECLARE CUR CURSOR FOR SELECT ID,MOJIRETU FROM HOGE;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
TRUNCATE T_TEMP;
OPEN CUR;
REPEAT
FETCH CUR INTO a, b;
IF NOT done THEN
SET J=2;
WHILE J<=LENGTH(b) DO
SET I=1;
WHILE I+J-1<=LENGTH(b) DO
INSERT INTO T_TEMP(NUM,MOJIRETU) VALUES(a,SUBSTR(b,I,J));
SET I=I+1;
END WHILE;
SET J=J+1;
END WHILE;
END IF;
UNTIL done END REPEAT;
CLOSE CUR;
END
//
DELIMITER ;

CALL INSERT_T_TEMP();

お礼日時：2011/10/04 22:42

No.1 回答者： yambejp 回答日時： 2011/10/03 19:58

常識的にSQLですることじゃないですね

できないことはないレベルの処理で、非常にめんどくさい割に
パフォーマンスも期待できませんがそれでもやりたいなら例示しますが？

ロジック的にはmojiretuの項目を2文字以上の文字になるよう
抽出する関数をつくり、総当たりでヒットさせるような流れでしょうか

この回答へのお礼

ご回答ありがとうございます。
＞パフォーマンスも期待できませんがそれでもやりたいなら例示しますが？
是非お願いしたいです。
ただ自分としてはMySQLなどで滅多に関数を使ったことがないので少し不安ですが、、^^;

お礼日時：2011/10/03 20:32