sql-server-2012 - SQL Server 2012 查询困惑

Question

我是 SQL 的初学者，我似乎无法针对这个问题提出正确的查询:

使用相关子查询为每个客户返回一行，代表客户最早的订单(日期最早的订单)。每行应包括以下三列:EmailAddress、OrderID 和 OrderDate。

我首先加入订单和客户表。 EmailAddress 是客户表中唯一需要的列。

      SELECT EmailAddress, OrderDate, orderID
      FROM Customers c JOIN orders o
      ON c.CustomerID = o.CustomerID

Answer 1

一个不太复杂的答案:

SELECT  EmailAddress, OrderID, OrderDate AS OldestOrder
  FROM  Customers AS C
        JOIN Orders AS O1
            ON C.CustomerID = O1.CustomerID
 WHERE  O1.OrderDate =
            (SELECT MIN(OrderDate)
             FROM Orders AS O2
             WHERE C.CustomerID = O2.CustomerID)