mysql jion使用别名错误_多个MySQL表JOIN / id-value替换/别名

我用INNER替换了LEFT JOINS，因为table_kingdoms中的所有ID都不为空，如果id可以为null，则返回LEFT，否则INNER更好。您的查询列和表名称与结构中的不同，因此我不确定使用哪个。因此，如果这不能满足您的需求，请指明您的意思不起作用，我们可以解决具体问题。

SELECT K.kid,K.Kingdom, P.Username as King,PQ.Username as Queen,

PP.Username as Prince,PS.Username as Princess

FROM table_kingdoms K

INNER JOIN TABLE_PLAYERS PK ON K.id_king = PK.pid --replace TABLE_PLAYERS with table_users depending on the actual table name

--LEFT JOIN table_users ON id_king = tu.id REPLACE WITH ABOVE

INNER JOIN TABLE_PLAYERS PQ ON K.id_queen = PQ.pid --replace TABLE_PLAYERS with table_users depending on the actual table name

--LEFT JOIN table_users ON id_queen = tu.id REPLACE WITH ABOVE

INNER JOIN TABLE_PLAYERS PP ON K.id_prince = PP.pid --replace TABLE_PLAYERS with table_users depending on the actual table name

--LEFT JOIN table_users ON id_prince = tu.id REPLACE WITH ABOVE

INNER JOIN TABLE_PLAYERS PS ON K.id_princess = PS.pid --replace TABLE_PLAYERS with table_users depending on the actual table name

--LEFT JOIN table_users ON id_princess = tu.id REPLACE WITH ABOVE就原始查询而言，它非常接近。最重要的是你说tu.id但是从来没有声明作为别名而且你不能重复使用相同的别名然后，你必须按照你想要的方式定义列，但这不是错误。这是我上面提到的实际错误的编辑：

SELECT *

FROM table_kingdoms

LEFT JOIN table_users tu ON id_king = tu.id

LEFT JOIN table_users tu2 ON id_queen = tu2.id

LEFT JOIN table_users tu3 ON id_prince = tu3.id

LEFT JOIN table_users tu4 ON id_princess = tu4.id就像一个注释，我个人也将table_kingdoms别名，这里也包括列显示：

SELECT K.kid,K.Kingdom, tu.Username as King, tu2.Username as Queen,

tu3.Username as Prince,tu4.Username as Princess

FROM table_kingdoms k

LEFT JOIN table_users tu ON k.id_king = tu.id

LEFT JOIN table_users tu2 ON k.id_queen = tu2.id

LEFT JOIN table_users tu3 ON k.id_prince = tu3.id

LEFT JOIN table_users tu4 ON k.id_princess = tu4.id