使用 LIKE 的 SQLite 模糊重复搜索 [英] SQLite fuzzy duplicate search using LIKE

问题描述

我有一个包含 4 个条目的表格.

I have a table with 4 entries.

    CREATE TABLE tab( 
    name Text 
                     ); 

    INSERT INTO "tab" VALUES('Intertek');
    INSERT INTO "tab" VALUES('Pntertek');
    INSERT INTO "tab" VALUES('Ontertek');
    INSERT INTO "tab" VALUES('ZTPay');

Pntterek &Ontertek 是拼写正确的 Intertek 的模糊复制品.我希望创建一个由模糊重复项和正确拼写的名称组成的列表.但是，如果 LIKE 搜索未找到模糊重复项，我不希望列表包含拼写正确的名称.

Pntertek & Ontertek are fuzzy duplicates of the correctly spelt Intertek. I wish to create a list consisting of fuzzy duplicates and the correctly spelt names. However, I don't want the list to contain the correctly spelt name if there is no fuzzy duplicate found by the LIKE search.

以下行告诉我有多少条目符合我的模糊搜索条件:

The following line tells me how many entries match my fuzzy search criteria:

    SELECT COUNT(name) 
    FROM tab 
    WHERE name LIKE '%ntertek' ;

    SELECT COUNT(name) 
    FROM tab 
    WHERE name LIKE '%TPay' ;

这很好用，分别给出 3 和 1.

This works fine and gives 3 and 1 respectively.

我知道下一部分是错误的，但它表达了我想要发生的事情:

I know this next part is wrong but it expresses what I want to happen:

    SELECT name 
    FROM tab 
    WHERE name LIKE '%ntertek'
    GROUP BY name 
    HAVING COUNT(name) FROM tab WHERE name LIKE '%ntertek' > 1 ; 

    SELECT name 
    FROM tab 
    WHERE name LIKE '%TPay'
    GROUP BY name 
    HAVING COUNT(name) FROM tab WHERE name LIKE '%TPay' > 1 ; 

在我看来，这应该创建以下列表:Intertek、Ontertek、Entertek.

In my mind this should create the following list: Intertek, Ontertek, Entertek.

但我得到了一个接近FROM":语法错误.

But I get a near "FROM": syntax error.

我对 sql 和编程一般来说有点新手，所以任何帮助将不胜感激.

I'm somewhat of a novice with sql and programming in general so any help would be greatly appreciated.

在此先感谢您的帮助.

推荐答案

如果你使用 GROUP BY name，你会为每个不同的 name 得到一个单独的组，并且那么相似的名字就数不清了.

If you use GROUP BY name, you will get a separate group for every distinct name, and then you will not be able to count similar names.

您应该使用子查询:

SELECT name
FROM tab
WHERE name LIKE '%ntertek'
  AND (SELECT COUNT(*)
       FROM tab
       WHERE name LIKE '%ntertek') > 1

这篇关于使用 LIKE 的 SQLite 模糊重复搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！