我试图为每个客户搜索最旧的未付发票,然后按发票日期的降序显示结果,但结果会显示最近发票的日期。我如何确保它能收到最早的发票日期?非常感谢。
这是我的问题:
$checkDebtors = "select
a.accountNumber as accountNumber, a.balanceOutstanding as balanceOutstanding, a.companyName as companyName, b.invoiceDate as invoiceDate, b.netOutstanding as netOutstanding
from
customersQQuote a
Right JOIN invoices b ON a.accountNumber = b.accountNumber
WHERE netOutstanding > 0 AND balanceOutstanding > 0
group by
a.accountNumber
order by
b.invoiceDate ASC";
您是指以下sql的结果吗?试试看,如果对你不起作用,请给我留言;)
select
a.accountNumber as accountNumber,
a.balanceOutstanding as balanceOutstanding,
a.companyName as companyName,
b.invoiceDate as invoiceDate,
c.netOutstanding as netOutstanding
from
(SELECT accountNumber, MIN(invoiceDate) AS invoiceDate FROM invoices GROUP BY accountNumber) b
LEFT JOIN customersQQuote a ON a.accountNumber = b.accountNumber
LEFT JOIN invoices c ON c.accountNumber = b.accountNumber AND c.invoiceDate = b.invoiceDate
WHERE c.netOutstanding > 0 AND a.balanceOutstanding > 0
group by
a.accountNumber
order by
b.invoiceDate ASC
上面的代码有一些错误,例如在where子句中使用别名和滥用group-by子句。你可以应用这段代码。。
select a.accountNumber as accountNumber, a.balanceOutstanding as balanceOutstanding,
a.companyName as companyName, b.invoiceDate as invoiceDate, b.netOutstanding as netOutstanding
from customersQQuote a Right JOIN invoices b ON a.accountNumber = b.accountNumber
WHERE b.netOutstanding > 0 AND a.balanceOutstanding > 0 order by b.invoiceDate;
您当前的代码不起作用,因为您没有指定要收回的发票。您已使用GROUP BY accountNumber,因此它将为每个accountNumber值返回一行。其他字段应该是聚合值(即,聚合函数(如MIN()或MAX())的结果)。如果GROUP BY子句和聚合函数的结果中都没有提到字段,那么MySQL将为该列返回一个值,但该值来自哪一行尚未定义。它可以是第一行或最后一行的值,也可以是介于两者之间的任何值(并且可以在不同版本的MySQL之间更改)。
大多数SQL风格都不允许这样的查询,并且会出错。MySQL有一个允许这样做的功能。但是这个功能可以配置为关闭
为了在标准SQL中实现这一点并给您一个一致的值,您可以使用子查询来获得每个accountNumber的第一个invoiceDate,使用MIN()聚合函数。然后,将此子查询与发票表连接,以获取其他列。
SELECT a.accountNumber,
a.balanceOutstanding,
a.companyName,
b.invoiceDate,
b.netOutstanding
FROM
(
SELECT accountNumber,
MIN(invoiceDate) as invoiceDate
FROM invoices
GROUP BY accountNumber
) sub0
INNER JOIN invoices b ON sub0.account_number = b.account_number AND sub0.invoiceDate = b.invoiceDate
INNER JOIN customersQQuote a ON sub0.account_number = a.account_number
请注意,如果一个accountNumber可能有两个相同的firstinvoiceDate值,那么您需要为每个日期选择另一个字段来最小化(即,可能是您唯一的id字段)。变得一团糟,但像这样:-
SELECT a.accountNumber,
a.balanceOutstanding,
a.companyName,
b.invoiceDate,
b.netOutstanding
FROM
(
SELECT accountNumber,
MIN(invoiceDate) as invoiceDate
FROM invoices
GROUP BY accountNumber
) sub0
INNER JOIN
(
SELECT accountNumber,
invoiceDate,
MIN(id) as id
FROM invoices
GROUP BY accountNumber, invoiceDate
) sub1 ON sub0.accountNumber = sub1.accountNumber AND sub0.invoiceDate = sub1.invoiceDate
INNER JOIN invoices b ON sub1.account_number = b.account_number AND sub1.invoiceDate = b.invoiceDate AND sub1.id = b.id
INNER JOIN customersQQuote a ON sub0.account_number = a.account_number
感谢您的建议。从你的回答中我得到了一些很好的提示,这些提示帮助我解决了这个问题。主要是使用了MIN(),而且我使用的是Right join而不是Left。此外,我只是在ORDER BY中使用了invoiceDate关键字,而不是b.invoiceDate。我想这确保了这里也使用了MIN规则。多亏了你,我终于得到了我需要的结果。
select
a.accountNumber as accountNumber, a.balanceOutstanding as balanceOutstanding, a.companyName as companyName, MIN(b.invoiceDate) as invoiceDate, b.netOutstanding as netOutstanding
from
customersQQuote a
Left JOIN invoices b ON a.accountNumber = b.accountNumber
WHERE netOutstanding > 0 AND balanceOutstanding > 0
group by
a.accountNumber
order by invoiceDate ASC