MySQL `INNER JOIN` 同一张表的倍数

Question

是否可以通过 INNER JOIN MySQL 查询来实现此结果？

我有一张包含策略的表格和一张包含成员的表格。 Strategy 表包含与 Member 表中的作者 ID 对应的作者 ID，以及更新现有作者作品的作者 ID。是否可以同时获取对这两个人的引用？像下面这样的东西，它没有返回错误，但也没有结果......

SELECT * FROM Strategies 
INNER JOIN Members AS a 
INNER JOIN Members AS b 
WHERE Strategies.ID='2' 
AND Strategies.AuthorID = a.ID 
AND Strategies.UpdateAuthorID = b.ID

Answer 1

使用LEFT JOIN:

SELECT 
  s.*,
  a.Name AS MemberName,
  b.Name AS UpdatedMemberName
FROM Strategies  AS s
LEFT JOIN Members AS a ON s.AuthorID = a.ID AND s.ID = 2 
LEFT JOIN Members AS b ON s.UpdateAuthorID = b.ID AND s.ID = 2 ;

如果您希望它们在一列中，请使用 COALESCE:

SELECT 
  s.*,
  COALESCE(a.Name, b.Name) AS MemberName
FROM Strategies  AS s
LEFT JOIN Members AS a ON s.AuthorID = a.ID AND s.ID = 2 
LEFT JOIN Members AS b ON s.UpdateAuthorID = b.ID AND s.ID = 2