选择multipl查询，每个查询得到2个JOIN

Question

我希望组合多个查询成为1:

SELECT b.fname FROM almanak as a
JOIN users as b on (b.userid = a.staf1)
SELECT c.fname FROM almanak as a
JOIN users as c on (c.userid = a.staf2)
SELECT d.fname FROM almanak as a
JOIN users as d on (d.userid = a.staf3)
SELECT e.fname FROM almanak as a
JOIN users as e on (e.userid = a.staf4)
SELECT f.fname FROM almanak as a
JOIN users as f on (f.userid = a.staf5)

我想让每个字段userid在另一个表上选择fname字段

Answer 1

这里有一种方法：

SELECT 
b.fname fname1, 
c.fname fname2, 
d.fname fname3,
e.fname fname4,
f.fname fname5
FROM almanak as a
INNER JOIN users as b ON b.userid = a.staf1
INNER JOIN users as c ON c.userid = a.staf2
INNER JOIN users as d ON d.userid = a.staf3
INNER JOIN users as e ON e.userid = a.staf4
INNER JOIN users as f ON f.userid = a.staf5

如果可能缺少关系，可以将INNER JOINs转换为LEFT JOINs。

或者，您可以进行条件聚合。假设almanak表的主键是id，它看起来像：

SELECT
MAX(CASE WHEN u.userid = a.staf1 THEN u.fname END) fname1,
MAX(CASE WHEN u.userid = a.staf2 THEN u.fname END) fname2,
MAX(CASE WHEN u.userid = a.staf3 THEN u.fname END) fname3,
MAX(CASE WHEN u.userid = a.staf4 THEN u.fname END) fname4,
MAX(CASE WHEN u.userid = a.staf5 THEN u.fname END) fname5
FROM almanak as a
INNER JOIN users as u 
ON u.userid IN (a.staf1, a.staf2, a.staf3, a.staf4, a.staf5)
GROUP BY a.id