mysql - 左联接列上的 SUM 限制了结果

Question

我有以下几个测试表的 SQL 查询:

SELECT salesperson.spnum, salesperson.spname, sales.quantity
FROM salesperson
LEFT JOIN sales ON sales.spnum = salesperson.spnum;

这将返回以下结果:

spnum   spname  quantity
1   "Bob Jansen"    5
1   "Bob Jansen"    10
1   "Bob Jansen"    1600
2   "Henk de Vries" NULL
3   "Anne de Graaf" NULL

但是，当我使用 SUM() 进行选择时:

SELECT salesperson.spnum, salesperson.spname, SUM(sales.quantity) AS quantity
FROM salesperson
LEFT JOIN sales ON sales.spnum = salesperson.spnum;

我只从 quantity 列不为空的行中获取结果:

spnum   spname  quantity
1   "Bob Jansen"    1615

如何防止 SUM() 像这样限制我的结果？我想获得 NULL 或“0”作为数量。

Answer 1

添加一个分组依据(这无论如何都应该运行)

SELECT salesperson.spnum, salesperson.spname, SUM(sales.quantity) AS quantity FROM salesperson
LEFT JOIN sales ON sales.spnum = salesperson.spnum
GROUP BY salesperson.spnum, salesperson.spname;

即使 null 也应该显示它们