mysql if语句如何

Question

我如何在mysql中编写这段代码？

如果tbl_comments.to那么一个数与tbl_users

加入，如果tbl_comments.to那么一个关键的球员与表人士和显示名称

加入，如果tbl_comments.to那么一个团队键与加盟表队和显示名称

如果tbl_comments.to是那么联赛键与tbl_sports

Answer 1

加入你怎么知道什么是tbl_comments.to，你有另一列保存类型？

或者您需要加入所有表格。

SELECT * FROM tbl_comments c 
LEFT JION ON tbl_users u ON c.to = u.id 
LEFT JION ON tbl_persons p ON c.to = p.id 
LEFT JION ON tbl_teams t ON c.to = t.id 
LEFT JION ON tbl_sports s ON c.to = s.id 

Answer 2

具有引用的几个可能的表一个列的做法称为多态关联。这是一种破坏关系数据库设计规则的黑客攻击。

根据您在给定行中找到的值，SQL不支持对不同表的条件连接。数据库解析查询时必须知道所有表。

您可以加入到所有那些与外连接的表，因为正是这些表应该包含匹配：

SELECT c.*, COALESCE(u.name, p.name, t.name, s.name) AS name 
FROM tbl_comments AS c 
LEFT OUTER JOIN tbl_users u ON c.to = u.id AND c.type = 'number' 
LEFT OUTER JOIN tbl_persons p ON c.to = p.id AND c.type = 'player' 
LEFT OUTER JOIN tbl_teams t ON c.to = t.id AND c.type = 'team' 
LEFT OUTER JOIN tbl_sports s ON c.to = s.id AND c.type = 'league'; 

或者你也可以加入到这些表之一，但限制你知道行匹配：

SELECT c.*, u.name 
FROM tbl_comments AS c 
INNER JOIN tbl_users u ON c.to = u.id 
WHERE c.type = 'number' 
    UNION ALL 
SELECT c.*, p.name 
FROM tbl_comments AS c 
INNER JOIN tbl_persons u ON c.to = p.id 
WHERE c.type = 'player' 
    UNION ALL 
SELECT c.*, t.name 
FROM tbl_comments AS c 
INNER JOIN tbl_teams t ON c.to = t.id 
WHERE c.type = 'team' 
    UNION ALL 
SELECT c.*, s.name 
FROM tbl_comments AS c 
INNER JOIN tbl_sports s ON c.to = s.id 
WHERE c.type = 'league'