mysql使用sum和max来查找支付最高的发票

Question

我正试图弄清楚如何从表中获得值总和的最大结果，以便显示收据的最高支付金额。我也应该使用subselect，但我感到困惑。到目前为止，这个查询为我显示了除了最大值之外的所有内容。这是需要使用的表格(发票+OD(这是我目前得到的这是我应该得到的

我在哪里可以加(和的(MAX((？

SELECT invoicenumber invoice
, invoicedate date
, SUM(orderqty*ordersell) AS 'Invoice Total' 
FROM invoice 
JOIN orderdetail 
ON invoice.invoiceid = orderdetail.invoiceid 
GROUP 
BY invoice ;

Answer 1

如果在SELECT中使用SUM或MAX，则需要按其他列分组：

SELECT invoicenumber AS invoice
, invoicedate AS date
, SUM(orderqty*ordersell) AS 'Invoice Total' 
FROM invoice 
JOIN orderdetail ON invoice.invoiceid = orderdetail.invoiceid 
GROUP BY invoicenumber,invoicedate;

Answer 2

您可以尝试这个(添加到前面的答案(：

SELECT invoicenumber AS invoice
, invoicedate AS date
, SUM(orderqty*ordersell) AS 'Invoice Total' 
FROM invoice 
JOIN orderdetail ON invoice.invoiceid = orderdetail.invoiceid 
GROUP BY invoicenumber,invoicedate ORDER BY `Invoice Total` DESC LIMIT 1;

可能效率不太高，但只返回最高结果。

Answer 3

我不知道这是否与你所看到的很接近，但我认为这会奏效(如果我正确理解你想要实现的目标(。

SELECT inv.invoicenumber,
inv.invoice, 
inv.invoicedate AS 'Date',
MAX((SELECT ord.orderqty+ord.ordersell FROM DUAL)) AS 'Invoice Total' 
FROM invoice inv
JOIN orderdetail ord
ON inv.invoice.invoiceid = ord.orderdetail.invoiceid 
GROUP BY inv.invoice, 
inv.invoice, 
inv.invoicedate;