MySql从表中获取唯一单词的列表，其中字段中的值用逗号分隔 [英] MySql get list of unique words from table where values in a field separated with comma

问题描述

我不确定使用纯SQL(MySQL)是否可行，但是无论如何我都会问.我有一张这样的桌子:

I am not sure if this is possible with pure SQL (MySQL) but I will ask anyway. I have a table like this:

ID    TAGS
-----------------------------
1     word1,word2,word3
2     word2,word4
3     word3,word5,word6,word7
...

我想从标签字段中选择一个唯一的单词，以获得类似这样的内容:

I would like to select a all unique words from tags field, to get out something like this:

TAGS
-----
word1
word2
word3
word4
word5
word6
word7

推荐答案

您可以在SQL中执行此操作，尽管它并不漂亮.

You can do this in SQL, although it is not pretty.

select distinct reverse(substring_index(reverse(substring_index(tags, ',', n.n)), ',', 1)) as word
from t cross join
     (select 1 as n union all select 2 as n union all select 3 as n union all select 4 as n) n
having word is not null

您需要确保子查询n的每个标签中至少包含单词数.

You need to be sure that the subquery n has at least the number of words in each tags.

此处是演示此操作的SQLFiddle.

Here is the SQLFiddle that demonstrates this.

这是将原始数据与序列号交叉连接.然后使用substring_index()从标签字符串中选择第n个值.

This is cross joining the original data with sequential numbers. It then picks out the nth value from the tags strings, using substring_index().

要获取最大数量的标签，您可以执行以下操作:

To get the maximum number of tags, you can do:

select max(length(tags) - length(replace(tags, ',', 1))+1
from t

这篇关于MySql从表中获取唯一单词的列表，其中字段中的值用逗号分隔的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！