带有联接的MySQL计数函数？

Question

我有两张桌子。用户(id，name)财务(id，item_id，user_id)

我的用例是

用户是组织的员工(销售人员)。

当他们出售一个项目财务表被更新的新记录，出售项目的串行id。

我想得到用户名和他们销售的总价值。

用户

id  |  name
1   |  Dinesh
2   |  Pathum
3   |  Naveed

金融

id  | item_id | amount | user_id
1   | 1       | 2000   | 1
2   | 2       | 2000   | 1
3   | 3       | 1000   | 3
4   | 4       | 500    | 3

预期产出

Dinesh 4000
Pathum 0
Naveed 1500

如何使用MySQL实现这一点？

Answer 1

查询如下：

SELECT u.name as 'Agent Name', 
if(sum(f.amount) IS NULL, 0,sum(f.amount)) as Total,
f.createdAt
FROM users u LEFT JOIN finance f
ON u.id = f.user_id
GROUP BY u.id, u.name, f.createdAt
ORDER BY f.createdAt DESC

这是一个工作的SQL Fiddle。

Answer 2

加入他们，组成他们，总结他们。

SELECT usr.name AS UserName, COALESCE(SUM(fin.amount),0) AS TotalAmount
FROM `User` usr
LEFT JOIN `Finance` fin ON fin.user_id = usr.id
GROUP BY usr.id, usr.name
ORDER BY usr.id;

db<>fiddle https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=1126abc85196d860230af37b2beaef9e的测试

Answer 3

另一种方式是：

SELECT Name,SUM(IFNULL(amount,0)) AS "Total" FROM (SELECT Name,amount FROM user LEFT JOIN finance ON user.id=finance.user_id) a GROUP BY Name;