MySQL拆分并加入值 [英] MySQL split and join the values

问题描述

我有一个[映射]表，其中包含2列，类似于下面

I have a table [mapping] with 2 columns similar to below

id  | values
1   | 1,2
2   | 1,2,3
3   | 1,1
4   | 1,1,2

和另一个表格[map]与此类似

and another table [map] is similar to this

sno | values
1   | Test
2   | Hello
3   | Hai

我的预期输出是

id  | values
1   | Test,Hello
2   | Test,Hello,Hai
3   | Test,Test
4   | Test,Test,Hello

有可能吗?如果可以的话，任何人都可以为我建立查询.

Is it possible? If it is please can anybody build a query for me.

推荐答案

您可以使用MySQL FIND_IN_SET() 联接表，并 GROUP_CONCAT() 来连接值:

You can use MySQL FIND_IN_SET() to join the tables and GROUP_CONCAT() to concat the values :

SELECT s.sno,GROUP_CONCAT(s.values) as `values` 
FROM mapping t
INNER JOIN map s ON(FIND_IN_SET(s.id,t.values))
GROUP BY s.sno

注意:您应该知道这是一个非常糟糕的数据库结构.这可能会导致更复杂的查询，并会迫使您过于复杂.您应该规范化您的数据，将其拆分，然后将每个ID放在单独的记录中！

Note: You should know that this is a very bad DB structure. This may lead to a lot more complicated queries and will force you to over complicate things. You should Normalize your data, split it , and place each ID in a separate record!

这篇关于MySQL拆分并加入值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！