MySQL:将CSV ID列表转换为CSV名称列表

Question

我有几个小组。每个组都有一些元素，每个元素都有一个ID和一个名称。

GROUPS
|___________|_______________|
|GROUP_NAME |ELEMENT_IDS    |
|___________|_______________|
|ala        |15,16,18       |
|bala       |18,15          |
|coala      |16             |

ELEMENTS
|___________|_______________|
|ID         |NAME           |
|___________|_______________|
|15         |foo            |
|16         |bar            |
|18         |car            |

为了输出以下内容，我应该做什么样的连接(在MySQL中)：

|___________|_______________|_________________________________|
|GROUP_NAME |ELEMENT_IDS    |ELEMENT_NAMES                    |
|___________|_______________|_________________________________|
|ala        |15,16,18       |foo,bar,car                      |
|bala       |18,15          |car,foo                          |
|coala      |16             |bar                              |

Answer 1

http://sqlfiddle.com/#!9/3aff79/7

SELECT g.group_name, g.element_ids,
GROUP_CONCAT(e.name) AS NAMES
FROM GROUPS as g 
INNER JOIN ELEMENTS as e ON
FIND_IN_SET(e.ID, g.ELEMENT_IDS)
GROUP BY GROUP_NAME;


| group_name | element_ids |       NAMES |
|------------|-------------|-------------|
|        ala |    15,16,18 | bar,foo,car |
|       bala |       18,15 |     car,foo |
|      coala |          16 |         bar |

希望这能有所帮助。