另一种方法是在你的连接条件中使用NOT EXISTS条件来测试以后的购买:

SELECT *
FROM customer c
LEFT JOIN purchase p ON (
       c.id = p.customer_id
   AND NOT EXISTS (
     SELECT 1 FROM purchase p1
     WHERE p1.customer_id = c.id
     AND p1.id > p.id
   )
)

如果你正在使用PostgreSQL，你可以使用DISTINCT ON来查找组中的第一行。

SELECT customer.*, purchase.*
FROM customer
JOIN (
   SELECT DISTINCT ON (customer_id) *
   FROM purchase
   ORDER BY customer_id, date DESC
) purchase ON purchase.customer_id = customer.id

PostgreSQL Docs - Distinct On

注意，DISTINCT ON字段——这里是customer_id——必须匹配ORDER BY子句中最左边的字段。

注意:这是一个非标准条款。

您也可以尝试使用子选择来完成此操作

SELECT  c.*, p.*
FROM    customer c INNER JOIN
        (
            SELECT  customer_id,
                    MAX(date) MaxDate
            FROM    purchase
            GROUP BY customer_id
        ) MaxDates ON c.id = MaxDates.customer_id INNER JOIN
        purchase p ON   MaxDates.customer_id = p.customer_id
                    AND MaxDates.MaxDate = p.date

选择应加入所有客户和他们的最后购买日期。

在SQL Server上你可以使用:

SELECT *
FROM customer c
INNER JOIN purchase p on c.id = p.customer_id
WHERE p.id = (
    SELECT TOP 1 p2.id
    FROM purchase p2
    WHERE p.customer_id = p2.customer_id
    ORDER BY date DESC
)

SQL Server小提琴:http://sqlfiddle.com/#!18/262fd / 2

在MySQL上你可以使用:

SELECT c.name, date
FROM customer c
INNER JOIN purchase p on c.id = p.customer_id
WHERE p.id = (
    SELECT p2.id
    FROM purchase p2
    WHERE p.customer_id = p2.customer_id
    ORDER BY date DESC
    LIMIT 1
)

MySQL小提琴:http://sqlfiddle.com/#!9/202613/7

我发现这条线索可以解决我的问题。

但当我尝试时，它们的表现很低。Bellow是我对更好的性能的建议。

With MaxDates as (
SELECT  customer_id,
                MAX(date) MaxDate
        FROM    purchase
        GROUP BY customer_id
)

SELECT  c.*, M.*
FROM    customer c INNER JOIN
        MaxDates as M ON c.id = M.customer_id 

希望这对你有所帮助。

您还没有指定数据库。如果它是一个允许分析函数的方法，那么使用这种方法可能比GROUP BY更快(在Oracle中肯定更快，在SQL Server的后期版本中很可能更快，不知道其他版本)。

SQL Server中的语法是:

SELECT c.*, p.*
FROM customer c INNER JOIN 
     (SELECT RANK() OVER (PARTITION BY customer_id ORDER BY date DESC) r, *
             FROM purchase) p
ON (c.id = p.customer_id)
WHERE p.r = 1