痔になりやすい生活習慣とは?

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

[テーブル]
mojiretuカラム

abcd
ab
cde
abcde
xy

このQ&Aに関連する最新のQ&A

A 回答 (4件)

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


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

(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);
    • good
    • 0
この回答へのお礼

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

>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

ちなみに


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)");
    • good
    • 0
この回答へのお礼

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

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

あ、一点補足・・・・



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));
    • good
    • 0
この回答へのお礼

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

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

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


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

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

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

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

このQ&Aに関連する人気のQ&A

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


人気Q&Aランキング