mysql - mysql sum()两个联接表，结果相乘

我有两张桌子；发票和发票项目。
发票中包含每个发票上的项目

例如：

invoices
----------------------------------
| id  |status| net | tax | total |
----------------------------------
| 72  |paid  | 100 | 120 |  220  |
| 73  |unpaid| 50  | 5   |  55   |
| 74  |paid  | 400 | 45  |  445  |
| 75  |paid  | 250 | 67  |  317  |


invoiceitems
-------------------------------
| invoiceid |itemdescription |
-------------------------------
| 72        | apples         |
| 72        | pears          |
| 72        | oranges        |
| 73        | lemons         |
| 73        | oranges        |

如您所见，在示例发票编号72中有3个项目

我想在发票中搜索某些内容，并显示某些字段的数量。

但我的问题是，总和值似乎乘以第二个表中的字段数。

$sql = "SELECT COUNT(DISTINCT invoices.id) AS num,
SUM(CASE invoices.status WHEN 'Paid' THEN 1 ELSE 0 END) AS numpaid,
SUM(CASE invoices.status WHEN 'Paid' THEN invoices.total ELSE 0 END) AS sumtotal,
FROM invoices
LEFT JOIN invoiceitems ON invoices.id=invoiceitems.invoiceid
WHERE invoices.id LIKE :invoiceid
AND IFNULL(opcinvoiceitems.itemdescription, '')  LIKE :itemdescription
AND invoices.net LIKE :net
AND invoices.tax LIKE :tax
AND invoices.total LIKE :total
AND ......"

因此使用上面的方法，发票72的总数将乘以3

我真的很抱歉，我知道这确实很难解释，但是我无法以任何其他方式解释它，一直在寻找年龄，但是找不到解决方案。希望有人可以帮忙。谢谢

最佳答案

一种您想要做的方法是在加入之前预聚集invoiceItems表：

SELECT COUNT(i.id) AS num,
       SUM(CASE i.status WHEN 'Paid' THEN 1 ELSE 0 END) AS numpaid,
       SUM(CASE i.status WHEN 'Paid' THEN i.total ELSE 0 END) AS sumtotal,
FROM invoices  i LEFT JOIN
     (select ii.invoiceid, sum(. . .) as . . .
      from invoiceitems ii
      where IFNULL(ii.itemdescription, '')  LIKE :itemdescription AND
      group by ii.invoiceid
     ) ii
     ON i.id = ii.invoiceid
WHERE i.id LIKE :invoiceid AND
      i.net LIKE :net  AND
      i.tax LIKE :tax AND
      i.total LIKE :total AND .....

您的查询实际上未在invoiceitems子句中使用from，因此很难提供更详细的示例。

关于mysql - mysql sum()两个联接表，结果相乘，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/18709104/