MYSQL跨多个字段连接2个表

它看起来像这样。我写MySQL查询已经有一段时间了。我相信有人可以改进它。

SELECT 
    a.id,
    a.name,
    b1.description,
    b2.description,
    b3.description,
    b4.description
FROM
    table_a `a`
        LEFT JOIN
    table_b `b1`
        ON a.part1 = b1.id
        LEFT JOIN
    table_b `b2`
        ON a.part2 = b2.id
        LEFT JOIN
    table_b `b3`
        ON a.part3 = b3.id
        LEFT JOIN
    table_b `b4`
        ON a.part4 = b4.id

您的数据模型从一开始就注定了您的复杂性。

select
*
from TableA A
left join TableB B1 on A.part1 = B1.id
left join TableB B2 on A.part2 = B1.id
left join TableB B3 on A.part3 = B1.id
left join TableB B4 on A.part4 = B1.id

这是未经测试的,但如果您“取消”TableA,则会有更多的可能性。 例如

SELECT
      A.id
    , A.Name
    , GROUP_CONCAT(B.Description ORDER BY A.rowno) as PartsList
FROM (
      SELECT
            A1.id
          , A1.Name
          , cj.rowno
          , CASE
                  WHEN cj.rowno = 1 THEN part1
                  WHEN cj.rowno = 2 THEN part2
                  WHEN cj.rowno = 3 THEN part3
                  WHEN cj.rowno = 4 THEN part4
            END AS LinkID
      FROM TableA a1
            CROSS JOIN ( 1 AS rowno
                  UNION ALL SELECT 2
                  UNION ALL SELECT 3
                  UNION ALL SELECT 4
                  ) cj
      ) A
      LEFT JOIN TableB B ON A.LinkId = B.ID
GROUP BY
      A.id
    , A.Name

而且,如果表A被永久规范化,您将不需要这些复杂性。