我有2张桌子，客户和交易。
Customer:
--------------------------------------------
ID | Name | Tel
--------------------------------------------
1 | Peter | 123 4567
2 | John | 456 1234
3 | Alice | 789 4561
4 | Amy | 741 8525
Transaction:
--------------------------------------------
CustID | Books | Pens | Ruler
--------------------------------------------
1 | 2 | 0 | 1
2 | 1 | 0 | 0
1 | 0 | 3 | 0
1 | 0 | 0 | 1
2 | 1 | 1 | 1
3 | 0 | 2 | 2
我需要以下
Results:
-------------------------------------------------------------------
ID | Name | Tel | Books | Pens | Ruler
-------------------------------------------------------------------
1 | Peter | 123 4567 | 2 | 3 | 2
2 | John | 456 1234 | 2 | 1 | 1
3 | Alice | 789 4561 | 0 | 2 | 2
4 | Amy | 741 8525 | 0 | 0 | 0
基本上，它将汇总同一客户的书籍，钢笔和尺子。
我试过了：
$sql = "select
`customer`.id,
`custmaster`.name,
`custmaster`.tel,
`transaction`.id,
`transaction`.books,
`transaction`.pens,
`transaction`.ruler,
from `customer`
left join `transaction`
on `customer`.id=`transaction`.custid
ORDER BY `customer`.id ASC";
但是什么都不显示。 :(我知道我需要在某个地方使用sum（）函数。有人可以帮忙吗？
最佳答案
使用SUM和GROUP BY。
SELECT c.id, c.name, c.tel, SUM(t.books) as books, SUM(t.pens) AS pens, SUM(t.ruler) AS ruler
FROM customer AS c
LEFT JOIN transactions AS t ON c.id = t.custid
GROUP BY c.id
ORDER BY c.id