mysql - sql 请求问题，多个 ID

Question

我有这两个表，我需要从 ENT 表中选择所有内容并显示技能名称而不是 ID。

skill_1、skill_2、skill_3引用技能表中的idSkill

感谢您的帮助

耳鼻喉科表:

+-------+---------+---------+---------+---------+
| idEnt | nameEnt | skill_1 | skill_2 | skill_3 |
+-------+---------+---------+---------+---------+
|     1 | testA   |       1 |      2  |      3  |
|     2 | testB   |       4 |      5  |      6  |
+-------+---------+---------+---------+---------+

技能表:

+---------+-------------+
| idSkill |  nameSkill  |
+---------+-------------+
|       1 | Storekeeper |
|       2 | Designer    |
|       3 | Teacher     |
|       4 | Baker       |
|       5 | Gardener    |
|       6 | Secretary   |
+---------+-------------+

Answer 1

使用此表结构，您需要在 ent 表上加入 skill 表 3 次，每次使用不同的别名:

select idEnt, nameEnt, s1.nameSkill as skill1Name, s2.nameSkill as skill2Name, s3.nameSkill as skill3Name
from ent e
inner join skill s1 on e.skill_1=s1.idSkill
inner join skill s2 on e.skill_2=s2.idSkill
inner join skill s3 on e.skill_3=s3.idSkill

您可能需要考虑更改表结构，并在保存 idEnt - idSkill 对的 ent 和 skill 表之间建立一个连接表。