选择逗号分隔字符串中的不同子字符串 |数据库 [英] Select distinct substrings that are in a comma separated string | MySql

问题描述

我有一个 csv 文件，我导入到我的数据库中，其中有一列用逗号分隔的值，我想要做的是从这些列中获取所有没有重复的值并将其放在只有一列中

I've a csv file that I imported to my database in which there are a column with comma separated values and what I wanna do is get all the values without duplicates from these column and put it in a only one column

这是桌子

这就是我想做的:

我不想修改原始列，只是在此形状中显示这些值，以了解逗号分隔的字符串可能采用的所有不同值

I don't want to modify the original column just show these values in this shape for know all diferentes values that the comma separated strings could take

我认为在这个问题 SQL 将值拆分为多行 中有答案.它提供了以下代码:

I think that in this question SQL split values to multiple rows there are the answer. It provides this code:

select
  tablename.id,
  SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.name, ',', numbers.n), ',', -1) name
from
  numbers inner join tablename
  on CHAR_LENGTH(tablename.name)
     -CHAR_LENGTH(REPLACE(tablename.name, ',', ''))>=numbers.n-1
order by
  id, n

但我无法深入理解.为什么有两个substring_index?n.numbers 有什么用?名字"有什么用?关键词?等

But I can't deeply understand it. Why there are two substring_index? What's it's the use of n.numbers? What's the use of "name" keyword? etc.

如果有人可以澄清我，或者如果在其他问题中更好地解释，那么在这种情况下，这个问题可以链接到那个或借用

It would greatly appreciated if someone can clarify me that or if in other question it better explained then in this case this question will can linked to that or borrow

推荐答案

为什么有两个 substring_index?

Why there are two substring_index?

想象一下 tablename.name = 'A,B,C' 和 numbers.n = 2.

查看内部函数调用的结果

Look the result of inner function call which is

SUBSTRING_INDEX(tablename.name, ',', numbers.n)
-- i.e.
SUBSTRING_INDEX('A,B,C', ',', 2)

它获取子部分直到指定的第 2 个分隔符 - 即它给出 'A,B'.

It gets the subpart until specified, 2nd, delimiter - i.e. it gives 'A,B'.

现在看看外部函数如何处理这个输出.这是

Now look what the outer function do with this output. It is

SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.name, ',', numbers.n), ',', -1)
-- i.e.
SUBSTRING_INDEX('A,B', ',', -1)

它从末尾获取子部分，直到指定的第一个分隔符 - 即它获取最后一个子字符串，即 'B'.

It gets the subpart until specified, 1st, delimiter, from the end - i.e. it gets last substring which is 'B'.

最后构造采用 numbers.n = 2 指定的第二个子串.

Finally the construction takes 2nd substring like specified by numbers.n = 2.

这篇关于选择逗号分隔字符串中的不同子字符串 |数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！