mysql - 字符更改MySQL后SQL提取数据

Question

我在一个表中有如下数据，列名是标题:

Acqua Di Parma Blu Mediterraneo Arancia Di Capri Scented Water EDT
Acqua Di Parma Blu Mediterraneo Arancia
Acqua Di Parma Blu Mediterraneo Bergamotto Di Calabria
Acqua Di Parma Blu Mediterraneo Cipresso Di Toscana Scented Water EDT
Acqua di Parma Blu Mediterraneo fico di amalfi
Acqua Di Parma Blu Mediterraneo Fico di Amalfi Scented Water EDT
Acqua Di Parma Blu Mediterraneo Mirto di Panarea
Acqua Di Parma Blu Mediterraneo Mirto di Panarea Scented Water EDT
Acqua Di Parma Blu Meditteraneo Cipresso
Acqua Di Parma Colonia Assoluta Bath
Acqua Di Parma Colonia Assoluta
Acqua Di Parma Colonia Body Cream
Acqua Di Parma Colonia Body Cream Tube
Adidas  Deep Energy
Adidas  Dynamic Pulse
Adidas  Fair Play

如您所见，这些都是 Acqua Di Parma Blu Mediterraneo 和阿迪达斯产品的所有变体

有没有办法逐个字母读取数据，然后当下一个字母出现不超过说3次时，返回字母变化前的内容

基本上，我只想读取这个列表并返回

Acqua Di Parma Blu Meditteraneo
Acqua Di Parma Colonia
Adidas  Deep Energy
Adidas  Dynamic Pulse
Adidas  Fair Play

整个表大约有 70,000 行都是相似的数据。

表格由row_id、title、category组成

可能吗？

非常感谢

达伦

Answer 1

好的 - 这不是很漂亮，也不确定它是否完全正确，但这是我能得到的最接近的。

我创建了一个单独的表，其中包含像这样的每组子字符串

create table subs as
select title, 
       substring_index(title, ' ',1) one,
       substring_index(title, ' ',2) two,
       substring_index(title, ' ',3) three,
       substring_index(title, ' ',4) four,
       substring_index(title, ' ',5) five,
       substring_index(title, ' ',6) six,
       substring_index(title, ' ',7) seven
  from title;

然后创建一个查询来检查一列的分组依据是否大于 1(即不唯一)，然后下一列的分组依据是否 = 1(即唯一)并且前一列是接下来，然后将每对列的结果合并在一起，最后在整个批处理中进行不同的选择

    select distinct brand from (
    select * from
    (select one brand, count(*) bcount
    from subs
    group by one) one,
    (select two prod, count(*) pcount
    from subs
    group by two) two
    where bcount > 1 
    and pcount=1
    and locate(one.brand, two.prod)>0
    union all
    select * from
    (select two brand, count(*) bcount
    from subs
    group by two) two,
    (select three prod, count(*) pcount
    from subs
    group by three) three
    where two.bcount > 1 
    and three.pcount=1
    and locate(two.brand, three.prod)>0
    union all
    select * from
    (select three brand, count(*) bcount
    from subs
    group by three) three,
    (select four prod, count(*) pcount
    from subs
    group by four) four
    where three.bcount > 1 
    and four.pcount=1
    and locate(three.brand, four.prod)>0
    union all
    select * from
    (select four brand, count(*) bcount
    from subs
    group by four) four,
    (select five prod, count(*) pcount
    from subs
    group by five) five
    where four.bcount > 1 
    and five.pcount=1
    and locate(four.brand, five.prod)>0
    union all
    select * from
    (select five brand, count(*) bcount
    from subs
    group by five) five,
    (select six prod, count(*) pcount
    from subs
    group by six) six
    where five.bcount > 1 
    and six.pcount=1
    and locate(five.brand, six.prod)>0
    union all
    select * from
    (select six brand, count(*) bcount
    from subs
    group by six) six,
    (select seven prod, count(*) pcount
    from subs
    group by seven) seven
    where six.bcount > 1 
    and seven.pcount=1
    and locate(six.brand, seven.prod)>0) x

结果如下

但它仍然存在一些问题，因为它同时显示了 Aqua Di Parma Blu 和 Aqua Di Parma Medit.. 两行而不是一行，所以它是不正确的。