mysql - 如何根据条件在两个表之间进行选择

说我有“ userBasic”，“ userMan”，“ userWoman”表。

“ userBasic”表具有名为“ userNum”和“ gender”的列。

我正在尝试根据userBasic表中的性别和userNum从userMan或userWoman检索数据。

简单的说，

select A.gender, B.nickname from userBasic A, (userman or userwoman) B
      where A.gender=? and B.userNum=A.userNum;

如果A.gender是男人->使用userman B，否则不使用userwomanB。

我该如何实现？

最佳答案

SELECT
    ub.gender,
    CASE
        WHEN ub.gender = 'male' THEN um.nickname
        ELSE uw.nickname
    END as nickname
FROM userBasic as ub
LEFT JOIN userMan as um ON ub.userNum = um.userNum
LEFT JOIN userWoman as uw on ub.userNum = uw.userNum

或者，如果您需要选择多个列并且不想做更多的case语句

SELECT
    ub.gender, um.nickname
FROM userBasic as ub
INNER JOIN userMan as um ON ub.userNum = um.userNum
UNION ALL
SELECT
    ub.gender, uw.nickname
FROM userBasic as ub
INNER JOIN userWoman as um ON ub.userNum = uw.userNum

关于mysql - 如何根据条件在两个表之间进行选择，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/19702089/