带有join的mysql count函数?

查询如下:

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 .

加入em,分组em,求和em。

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;

测试 小提琴 here

另一种方式:

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;