MySQL语句内连接

Question

我有一条SQL语句

SELECT
    locales.id_c,
    locales.name_p AS NombrePropietario,
    locales.surname1 AS PrimerApellido,
    locales.local_name AS Nombre_Local,
    locales.zona AS ZonaLiga,
    locales.id_m1 AS Maquina_1,
    maquinas.tipo_m AS Tipo_de_Maquina1,
    locales.id_m2 AS Maquina_2,
    maquinas.tipo_m AS Tipo_de_Maquina2,
    locales.id_m3 AS Maquina_3,
    locales.id_m4 AS Maquina_4
FROM locales
INNER JOIN maquinas
    ON locales.id_m1 = maquinas.id_m;

在本例中，它只显示了"Maquina_1“和"Tipo_de_Maquina1”

并且需要显示"Maquina_2“和"Tipo_de_Maquina2"，"Maquina_3”和"Tipo_de_Maquina3“等。

有人能帮帮我吗？

Answer 1

使用表别名多重连接maqinas表：

SELECT
  l.id_c,
  l.name_p AS NombrePropietario,
  l.surname1 AS PrimerApellido,
  l.local_name AS Nombre_Local,
  l.zona AS ZonaLiga,
  l.id_m1 AS Maquina_1,
  m1.tipo_m AS Tipo_de_Maquina1,
  l.id_m2 AS Maquina_2,
  m2.tipo_m AS Tipo_de_Maquina2,
  l.id_m3 AS Maquina_3,
  m3.tipo_m AS Tipo_de_Maquina3,
  l.id_m4 AS Maquina_4,
  m4.tipo_m AS Tipo_de_Maquina4
FROM locales l
LEFT JOIN maquinas m1 ON l.id_m1 = m1.id_m
LEFT JOIN maquinas m2 ON l.id_m2 = m2.id_m
LEFT JOIN maquinas m3 ON l.id_m3 = m3.id_m
LEFT JOIN maquinas m4 ON l.id_m4 = m4.id_m
;

我使用外部连接的情况下，记录没有maquina 1，2，3或4。如果每个记录总是有所有的maquina，你可以切换到内部连接。

无论如何，您可能想要考虑另一个数据模型，在这个数据模型中，您将添加一个表locale_maquina，并从locales表中删除单个id_m1 to id_m4。这将使选择数据变得更容易，并支持链接到某个位置的灵活数量的maquinas。

Answer 2

您的"Tipo_de_Maquina2“是错误的，因为它将显示与"Tipo_de_Maquina1”相同的值。

您应该遵循与"Tipo_de_Maquina1“相同的INNER JOIN逻辑，以获取tipo_m的其他值。

SELECT
    locales.id_c,
    locales.name_p AS NombrePropietario,
    locales.surname1 AS PrimerApellido,
    locales.local_name AS Nombre_Local,
    locales.zona AS ZonaLiga,
    locales.id_m1 AS Maquina_1,
    m1.tipo_m AS Tipo_de_Maquina1,
    locales.id_m2 AS Maquina_2,
    m2.tipo_m AS Tipo_de_Maquina3,
    locales.id_m3 AS Maquina_3,
    m3.tipo_m AS Tipo_de_Maquina4,
    locales.id_m4 AS Maquina_4,
    m4.tipo_m AS Tipo_de_Maquina4
FROM locales
INNER JOIN maquinas AS m1
    ON locales.id_m1 = m1.id_m
INNER JOIN maquinas AS m2
    ON locales.id_m2 = m2.id_m
INNER JOIN maquinas AS m3
    ON locales.id_m3 = m3.id_m
INNER JOIN maquinas AS m4
    ON locales.id_m4 = m4.id_m;