mysql - 如何分别用他们的名字替换逗号分隔的部门 ID？

Question

我的表格是这些:

员工表:

+-----------+----------+------------+
| id       | name     | department  |
+-----------+----------+------------+
| 1        | Carrera  | 1           |
| 2        | Taylor   | 1,2         |
+-----------+----------+------------+

部门表:

+--------+-------+
| id     | name  |
+--------+-------+
|   1    |  CS   |
|   2    |  IT   |
+--------+-------+

员工表和部门表的输出:

+----+------------+-------------+
| id | name       | department  |
+----+------------+-------------+
|  1 | Carrera    |   CS        |
|  2 | Taylor     |   CS,IT     |
+----+------------+-------------+

Answer 1

您应该避免将数据存储为逗号分隔值，并遵循规范化。

但是在这种情况下你可以做一些事情

select 
e.id , 
e.name , 
group_concat(d.name) from employee e 
left join department d on find_in_set(d.id,e.department) 
group by e.id ;