MySQL 在 INNER JOIN 中使用 INNER JOIN 和 CASE WHEN 选择

Question

我有一个包含字符的数据库。由于角色的名称不仅在名称与角色分开时才使用。由于有 Male 和 Female 字符，我将它们保存在不同的表中:

tbl_player, tbl_maleName, tbl_femaleName

在我的 MySQL Select 查询中，我想根据他们的性别选择带有 INNER JOIN 的正确表。所以像这样:(SQL 查询是错误的，不起作用:( )

 SELECT tbl_player.id, 
(CASE tbl_player.gender
    WHEN 'm' THEN tbl_maleName.name
    WHEN 'f' THEN tbl_femaleName.name
END) as gamerName
, tbl_player.gender
FROM tbl_player
INNER JOIN
(CASE tbl_player.gender
    WHEN 'm' THEN tbl_maleName ON tbl_player.gamerName = tbl_maleName.id
    WHEN 'f' THEN tbl_femaleName ON tbl_player.gamerName = tbl_femaleName.id
 END)

提前致谢!

Answer 1

我不确定为什么您选择使用 2 个表来表示名称，但我不推荐这样做。

但是，试试这个:

SELECT
      tbl_player.id
    , (CASE tbl_player.gender WHEN 'm' THEN tbl_maleName.name WHEN 'f' THEN tbl_femaleName.name END) AS gamerName
    , tbl_player.gender
FROM tbl_player
      LEFT JOIN tbl_maleName ON tbl_player.gamerName = tbl_maleName.id
                  AND tbl_player.gender = 'm'
      LEFT JOIN tbl_femaleName ON tbl_player.gamerName = tbl_femaleName.id
                  AND tbl_player.gender = 'f'