Mysql在运行时根据列动态复制行

Question

想象一下下面的表结构:

约会

+----+-------+
| id | name  |
+----+-------+
|  1 | test1 |
|  2 | test2 |
|  3 | test3 |
+----+-------+

appointment_carers

+----+----------------+--------+--------+--------+
| id | appointment_id | carer1 | carer2 | carer3 |
+----+----------------+--------+--------+--------+
|  1 |              1 |      1 |      2 |      3 |
|  2 |              2 |      4 |      5 |      6 |
+----+----------------+--------+--------+--------+

不幸的是，我现在无法改变这些表的现有结构。

有没有一种方法可以查询所有预约，并且对于每个看护人时段，我都可以得到重复的预约行？

下面是查询预期结果的示例:

+----+-------+-------+
| id | name  | carer |
+----+-------+-------+
|  1 | test1 |     1 |
|  1 | test1 |     2 |
|  1 | test1 |     3 |
|  2 | test2 |     4 |
|  2 | test2 |     5 |
|  2 | test2 |     6 |
+----+-------+-------+

Answer 1

select id, name, carer from (
select appointment_id, carer1 as carer from appointment_carers
union
select appointment_id, carer2 as carer from appointment_carers
union
select appointment_id, carer3 as carer from appointment_carers
) as carers
join appointments on id = appointment_id;