sql - 如何在不使用 SQL 中的相关子查询的情况下获取最旧的日期？

Question

以下代码列出了所有发票，我只想要供应商的最旧发票:

SELECT DISTINCT vendor_name, i.invoice_number AS OLDEST_INVOICE, 
    MIN(i.invoice_date), i.invoice_total
FROM vendors v
JOIN invoices i
ON i.vendor_id = v.vendor_id
GROUP BY vendor_name, invoice_number, invoice_total
ORDER BY MIN(i.invoice_date);

Answer 1

我们将使用 ROW_NUMBER()按每个供应商的日期对发票进行“排名”，然后只选择每个供应商最早的发票:

SELECT vendor_name, invoice_number AS oldest_invoice, invoice_date, invoice_total
  FROM vendors v
 INNER JOIN (SELECT invoices.*,
                    ROW_NUMBER() OVER (PARTITION BY vendor_id ORDER BY invoice_date ASC)
                      AS rn
               FROM invoices) i
       ON i.vendor_id = v.vendor_id
          AND
          i.rn = 1;