mysql - 排序结果基于连接条件顺序

Question

有3个mysql表。

BOOKS have ID, AUTHOR1, AUTHOR2, PUBLISHER1, PUBLISHER2
AUTHORS have ID, WEBSITE
PUBLISHERS have ID, WEBSITE

并非所有出版商或作者都有网站。

我有一个查询，返回给定图书 ID 的作者和出版商网站的匹配集。如果他们都有一个网站，这将意味着 2 行。

SELECT AUTHORS.WEBSITE as WEB1, PUBLISHERS.WEBSITE as WEB2
FROM BOOKS
LEFT OUTER JOIN AUTHORS ON (ID=AUTHOR1 OR ID=AUTHOR2)
LEFT OUTER JOIN PUBLISHERS ON (ID=PUBLISHER1 OR ID=PUBLISHER2)
WHERE BOOK.ID ='12345'
LIMIT 2

虽然这行得通，但它最终会返回与第一作者和出版商之前的第二作者和出版商匹配的行。可能基于字母顺序。

如何让这个查询首先返回包含 AUTHOR1 和 PUBLISHER1 网站的行？

Answer 1

我会将它们视为单独的联接并使用 COALESCE。

SELECT COALESCE(A1.WEBSITE, A2.WEBSITE) as WEB1, 
       COALESCE(P1.WEBSITE, P2.WEBSITE) as WEB2
FROM BOOKS B
LEFT OUTER JOIN AUTHORS A1 ON A1.ID=B.AUTHOR1 
LEFT OUTER JOIN AUTHORS A2 ON A2.ID=B.AUTHOR2
LEFT OUTER JOIN PUBLISHERS P1 ON P1.ID=B.PUBLISHER1 
LEFT OUTER JOIN PUBLISHERS P2 ON P2.ID=B.PUBLISHER2
WHERE B.ID ='12345'