php - 从mysql检索后，如何将与公共(public)id连接的多行合并为一行

Question

如果社区能够帮助我解决以下我无法解决的问题，我将非常感激。

执行查询后从mysql检索到的数据格式如下:

id    |  invdate       |   product  |  total  |  amountpaid  |  balance
----------------------------------------------------------------------
1        01/04/2015       choco      10000        2000         8000
1        01/04/2015       choco      10000        5000         3000
2        03/04/2015       apple       5000        1000         4000
2        03/04/2015       apple       5000        2000         2000

对于上述结果，我使用的mysql查询如下:

SELECT tprojects.grand_total AS gtot, payments.amount_paid AS apaid, payments.balance AS bal, tprojects.CreatedDate, tprojects.IdProject, tprojects.product, tprojects.vat, tprojects.vtf, tprojects.ProjectStatus, tclients.Company FROM tprojects INNER JOIN payments ON payments.projectid = tprojects.IdProject INNER JOIN tclients ON tclients.IdClient = tprojects.client_id

那么我如何使查询结果如下以便打印以供查看:

id|  invdate         |   product   |  total  |  amountpaid  |  balance
1        01/04/2015        choco     10000       7000          3000 
2        03/04/2015        apple      5000       3000          2000

我非常感谢社区在这方面的帮助。过去两天我一直在做这个。

提前致谢。

Answer 1

select id, invdate, product, total, sum(amountpaid), total - sum(amountpaid)
  from invoices
  group by id, invdate, product, total;

example here

请注意，如果发票上包含多个产品，则此方法将无法正常工作 - 但您的架构并未真正设置为能够很好地处理这些情况。事实上，总的来说，它可以设置得更好。