通过两种方式对联接表进行排序搜索 - Ordering search of joined tables in 2 ways

我试图为每个客户搜索最旧的未付发票，然后按发票日期的降序显示结果，但结果会显示最近发票的日期。我如何确保它能收到最早的发票日期？非常感谢。

这是我的问题：

$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