mysql - MySQL-如何执行多重联接查询？

我正在尝试联接两个表，但问题是出纳员表可以与sales表中的3个字段联接，如果我只将一个表与两个表联接在一起，我就不会错过任何记录。我只想获得与收银员表一样多的行。我希望这是有道理的。这是我正在尝试做的一个例子。

SELECT cashiers.cashierId,cashiers.name,cashiers.age,
(SELECT
(SELECT SUM(amount) * .20 FROM sales WHERE cashierId1 = cashiers.cashierId) +
(SELECT SUM(amount) * .30 FROM sales WHERE cashierId2 = cashiers.cashierId) +
(SELECT SUM(amount) * .40 FROM sales WHERE cashierId3 = cashiers.cashierId)
) AS totalAmount
FROM cashiers

谢谢大家的帮助。

最佳答案

我认为戈登的答案可能不错，但我认为它也假设只有一位出纳员可以匹配。您可能一直在想像这样的事情：

select
    c.cashierid, min(c.name) as name, min(c.age) as age,
    sum(s1.amount * 0.2) + sum(s2.amount * 0.3) + sum(s3.amount * 0.4)
from
    cashiers c
    left outer join sales s1 on s1.cashierid1 = c.cashierid
    left outer join sales s2 on s2.cashierid2 = c.cashierid
    left outer join sales s3 on s3.cashierid3 = c.cashierid
group by
    c.cashierId