MySQL LEFT JOIN 与 IF

Question

我有这个问题:

SELECT r.*, d.rateName
FROM ratings r
LEFT JOIN rate_data d ON (r.rateID=d.rateID AND d.rateName != '')
ORDER BY r.rateTime DESC

这个工作正常。现在我有了第二个数据表。在一个查询中使用 IF 进行更改，还是进行两个查询并合并结果的唯一方法？

我试着向你展示我的意思:

SELECT r.*, d.rateName
FROM ratings r
(IF r.isOther == 0)LEFT JOIN rate_data d ON (r.rateID=d.rateID AND d.rateName != '')
(ELSEIF r.isOther == 1)LEFT JOIN rate_data_other d ON (r.rateOtherID=d.rateOtherID AND d.rateName != '')
ORDER BY r.rateTime DESC

谢谢你的帮助

Answer 1

SELECT r.*, IF(r.isOther,do.rateName,d.rateName)
FROM ratings r
LEFT JOIN rate_data d ON (r.rateID = d.rateID AND d.rateName != '')
LEFT JOIN rate_data_other do ON (r.rateOtherID = do.rateOtherID AND do.rateName != '')
ORDER BY r.rateTime DESC;

或者

SELECT r.*, 
(CASE 
 WHEN r.isOther = 0 THEN d.rateName
 ELSE do.rateName
 END) as rateName
FROM ratings r
LEFT JOIN rate_data d ON (r.rateID = d.rateID AND d.rateName != '')
LEFT JOIN rate_data_other do ON (r.rateOtherID = do.rateOtherID AND do.rateName != '')
ORDER BY r.rateTime DESC;