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

How do I get the oldest date without using a correlated subquery in SQL?

提问by Marc Gordon

The following code list all the invoices, and I just want the oldest invoice from a vendor:

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

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);

回答by Jonathan Leffler

Time for TDQD — Test-Driven Query Design

TDQD 时间 - 测试驱动的查询设计

The minimum date for an invoice for each vendor is given by:

每个供应商的发票的最短日期由以下给出：

SELECT vendor_id, MIN(invoice_date) AS invoice_date
  FROM invoices
 GROUP BY vendor_id

The corresponding minimum invoice number (given that there could have been several invoices sent on the first day that a vendor was invoiced, if invoice_dateis a true DATE with no time component; if the DATE includes a time component, the second MIN() is probably unnecessary), is:

相应的最小发票编号（假设在向供应商开具发票的第一天可能有几张发票，如果invoice_date是没有时间部分的真实 DATE；如果 DATE 包含时间部分，则第二个 MIN() 可能是不必要的），是：

SELECT vendor_id, MIN(invoice_number) AS invoice_number
  FROM invoices AS i
  JOIN (SELECT vendor_id, MIN(invoice_date) AS invoice_date
          FROM invoices
         GROUP BY vendor_id
       ) AS j ON j.vendor_id = i.vendor_id AND j.invoice_date = i.invoice_date
 GROUP BY vendor_id

You can join this expression with other tables to suit your query requirements:

您可以将此表达式与其他表连接以满足您的查询要求：

SELECT v.*, i.*
  FROM vendors AS v
  JOIN (SELECT vendor_id, MIN(invoice_number) AS invoice_number
          FROM invoices AS i
          JOIN (SELECT vendor_id, MIN(invoice_date) AS invoice_date
                  FROM invoices
                 GROUP BY vendor_id
               ) AS j ON j.vendor_id = i.vendor_id AND j.invoice_date = i.invoice_date
         GROUP BY vendor_id
       ) AS inv_info ON v.vendor_id = inv_info.vendor_id
  JOIN invoices AS i ON i.invoice_number = inv_info.invoice_number

There are undoubtedly other ways to design it. Note that none of these sub-queries are correlated sub-queries.

毫无疑问，还有其他设计方法。请注意，这些子查询都不是相关的子查询。

The TDQD has been purely nominal; no DBMS was troubled with checking whether these queries are syntactically valid, much less return the correct answer. OTOH, it is a standand technique.

TDQD 纯粹是名义上的；没有 DBMS 会为检查这些查询在语法上是否有效而烦恼，更不用说返回正确答案了。OTOH，这是一种标准技术。

If you like listing lots of columns in GROUP BY clauses, you could do without the final join to invoicesby having the inv_infosub-query return the relevant invoice columns. I don't like having to write lots of column names out — but if I was worried about performance, I'd measure to see if it made a significant difference.

如果您喜欢在 GROUP BY 子句中列出大量列，您可以invoices通过让inv_info子查询返回相关发票列来取消最终连接。我不喜欢必须写出很多列名——但如果我担心性能，我会衡量它是否有显着差异。

You might find that there's an OLAP function/query that will do the job notationally quicker.

您可能会发现有一个 OLAP 函数/查询可以更快地完成工作。

回答by pilcrow

We'll use ROW_NUMBER()to "rank" the invoices by date per vendor, and then select only the oldest per vendor:

我们将使用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;

回答by Mahmoud Gamal

Try this instead:

试试这个：

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

回答by wildplasser

Would not HAVINGwork here?

不会HAVING在这里工作吗？

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
HAVING i.invoice_date = MIN (i.invoice_date)
ORDER BY MIN(i.invoice_date);