質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

89.19%

SQL での重複項目検索

受付中

回答 2

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 375

m2677024688

score 5

前提・実現したいこと

SQLでの名前のあいまい検索で重複を探したいです。

バージョン

mysql
5.7.22

該当のソースコード

-- auto-generated definition
create table table_name
(
  id  int          null,
  sei varchar(100) null,
  mei varchar(100) null
)
charset = utf8mb4;


insert table_name (id, sei, mei) VALUE (1, '田中', '二郎');
insert table_name (id, sei, mei) VALUE (2, '田中', '二郎2');
insert table_name (id, sei, mei) VALUE (3, '田中', '二郎3');
insert table_name (id, sei, mei) VALUE (4, '田中', '二郎4');
insert table_name (id, sei, mei) VALUE (5, '高橋', '太郎');
insert table_name (id, sei, mei) VALUE (6, '高橋', '太郎2');
insert table_name (id, sei, mei) VALUE (7, '高橋', '太郎3');
insert table_name (id, sei, mei) VALUE (8, '高橋', '太郎4');
insert table_name (id, sei, mei) VALUE (9, '斎藤2', '一郎9');
insert table_name (id, sei, mei) VALUE (10, '斎藤2', '一郎');
insert table_name (id, sei, mei) VALUE (11, '斎藤2', '一郎2');
insert table_name (id, sei, mei) VALUE (12, '斎藤2', '一郎3');
insert table_name (id, sei, mei) VALUE (13, '佐藤2', '四郎');
insert table_name (id, sei, mei) VALUE (14, '佐藤2', '四郎2');
insert table_name (id, sei, mei) VALUE (15, '佐藤2', '四郎3');
insert table_name (id, sei, mei) VALUE (16, '佐藤2', '四郎4');
insert table_name (id, sei, mei) VALUE (17, '佐藤2', '四郎5');
insert table_name (id, sei, mei) VALUE (18, '佐藤2', '四郎6');




SELECT
  CONCAT(sei, mei) as fullname
FROM table_name
GROUP BY fullname
HAVING COUNT(*) > 1;

試したこと

SELECT
  CONCAT(sei, mei) as fullname,
  count(fullname) as count
FROM table_name
GROUP BY fullname
HAVING COUNT(*) > 1;

期待値

id fullname count
1 田中二郎 4
10 高橋2太郎 4
1 斎藤2一郎 4
10 佐藤2四郎 5

・条件

sei : が完全一致
mei : 前方一致

上記の名前名前苗字が18レコードあることを確認できるようにしたいです。

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • m.ts10806

    2019/09/09 16:26

    というよりmeiの「部分一致」がネックです。
    前方一致、後方一致ならまだやりやすいかもしれませんが、「部分一致」となるとそれこそ「苗字だけ合ってればOK」ということにもなりえます。
    ここは「どの程度の部分一致を許容するのか」の要件を詰めないとプログラム側でもどうにもできないと思います。

    キャンセル

  • m2677024688

    2019/09/09 17:21

    自分もそのように定義があいまいだったため、前方一致で進めることにしました。
    良い方法はありますでしょうか?

    キャンセル

  • m.ts10806

    2019/09/09 17:24

    となると「どこまで許容するか」を詰めていく必要があります。
    何文字まで合ってればよいのか
    太郎と太朗は同じとするのか。太朗太と太郎太は同じとするのか・・とか。
    ここは「決め」になってきますので、やはり仕様を詰めるしかないと思います。
    近似値を求めるのは数字は割と楽ですが文字となるとかなり困難になると思います。

    もっと言えば「これを実現して何がしたいのか」という大目的部分も詰める必要がありそうです。

    キャンセル

回答 2

0

まず「名前苗字:9」「名前2苗字:9」にはならない。8:10です
またグループ処理をしたらどのidをひろうか指定しないとだめ
一番小さいのでよいのでしょうか?

select min(id) as id,concat(sei,'苗字') as fullname,count(*) as cnt from table_name
where mei like '苗字%'
group by fullname 
order by id

sample

  • 元データ
create table tbl(
  id  int primary key,
  sei varchar(100) null,
  mei varchar(100) null
);
insert into tbl (id, sei, mei) VALUE
(1, '田中', '二郎'),
(2, '田中', '二郎2'),
(3, '田中', '二郎3'),
(4, '田中', '二郎4'),
(5, '高橋', '太郎'),
(6, '高橋', '太郎2'),
(7, '高橋', '太郎3'),
(8, '高橋', '太郎4'),
(9, '斎藤2', '一郎9'),
(10, '斎藤2', '一郎'),
(11, '斎藤2', '一郎2'),
(12, '斎藤2', '一郎3'),
(13, '斎藤2', '一'),
(14, '斎藤2', '一二三'),
(15, '斎藤2', '二郎');
  • 抽出
select min(id) as id,concat(sei,substr(mei,1,1)) as fullname,count(*) as cnt from tbl
group by fullname 
order by id

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/09/09 17:58

    斎藤が「一郎」だとして
    「一二三」がいたら「一」で一緒になるので一緒なの?
    つまり1文字目だけあってればよいのでしょうか?

    キャンセル

  • 2019/09/09 18:09

    はい。
    その認識であっております。

    キャンセル

  • 2019/09/09 18:27 編集

    じゃ、斎藤は「斎藤2一」なんですね?
    sample確認ください

    キャンセル

0

やりたいことは、FULLTEXTのMATCH AGAINSTな気もしますが、
テーブル仕様が違うので、今回の条件内で、行う場合。

SELECT    T1.id
    ,    T1.sei
    ,    T1.mei
    ,    (
            SELECT    COUNT( 'X' )
            FROM    table_name T3
            WHERE    T3.sei    = T1.sei
                AND    T3.mei    like concat( '%'
                                    ,    ifnull(SUBSTR(T1.mei,1,1), '' )
                                    ,     '%'
                                    ,    ifnull(SUBSTR(T1.mei,2,1), '' )
                                    ,     '%'
                                    ,    ifnull(SUBSTR(T1.mei,3,1), '' )
                                    ,     '%'
                                    ,    ifnull(SUBSTR(T1.mei,4,1), '' )
                                    ,     '%'
                                    ,    ifnull(SUBSTR(T1.mei,5,1), '' )
                                    ,     '%'
                                    ,    ifnull(SUBSTR(T1.mei,6,1), '' )
                                    )
        ) as cnt
FROM    table_name T1
WHERE    EXISTS(
            SELECT    'X'
            FROM    table_name T2
            WHERE    T2.id    <> T1.id
                AND    T2.sei    = T1.sei
                AND    T2.mei    like concat( '%'
                                    ,    ifnull(SUBSTR(T1.mei,1,1), '' )
                                    ,     '%'
                                    ,    ifnull(SUBSTR(T1.mei,2,1), '' )
                                    ,     '%'
                                    ,    ifnull(SUBSTR(T1.mei,3,1), '' )
                                    ,     '%'
                                    ,    ifnull(SUBSTR(T1.mei,4,1), '' )
                                    ,     '%'
                                    ,    ifnull(SUBSTR(T1.mei,5,1), '' )
                                    ,     '%'
                                    ,    ifnull(SUBSTR(T1.mei,6,1), '' )
                                    )
            )

実行結果

id sei mei cnt
1 田中 二郎 4
5 高橋 太郎 4
10 斎藤2 一郎 4
13 佐藤2 四郎 6

ちなみに、高橋2なんて結果は出ませんし、佐藤2四朗も6ですよね?
机上の期待値はよく確認してくださいね。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 89.19%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる