mysql - 我希望返回名称而不是ID

我有三张桌子，我想加入。
检查这个小提琴：http://sqlfiddle.com/#!2/20410/2
列中：segunda、terca、quarta、quinta和sexta
而不是数字。。。我需要“材料”表中的名字。
但我查了同一张“材料”表上的所有身份证
有可能吗？有人能帮我吗？

最佳答案

使用内部连接和使用子查询可以通过两种方式完成此操作。
使用内部联接：

SELECT
h.id_classe,
h.id_aula,
s.serie,
m1.materia as segunda,
m2.materia as terca,
m3.materia as quarta,
m4.materia as quinta,
m5.materia as sexta
FROM horario_de_aulas h
INNER JOIN materias as m1 on h.segunda = m1.id_materia
INNER JOIN materias as m2 on h.terca = m2.id_materia
INNER JOIN materias as m3 on h.quarta = m3.id_materia
INNER JOIN materias as m4 on h.quinta = m4.id_materia
INNER JOIN materias as m5 on h.sexta = m5.id_materia
INNER JOIN series s ON h.classe = s.id_serie = 1
LIMIT 4

使用子查询

SELECT
h.id_classe,
h.id_aula,
s.serie,
(select materia from materias where id_materia=h.segunda) as segunda,
(select materia from materias where id_materia=h.terca) as terca,
(select materia from materias where id_materia=h.quarta) as quarta,
(select materia from materias where id_materia=h.quinta) as quinta,
(select materia from materias where id_materia=h.sexta) as sexta
FROM horario_de_aulas h
INNER JOIN materias m
INNER JOIN series s ON h.classe = s.id_serie = 1
LIMIT 4