另一种方法是在你的连接条件中使用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
   )
)

在SQLite上测试:

SELECT c.*, p.*, max(p.date)
FROM customer c
LEFT OUTER JOIN purchase p
ON c.id = p.customer_id
GROUP BY c.id

max()聚合函数将确保从每个组中选择最新的购买(但假设日期列的格式是max()给出最新的—通常情况下是这样)。如果你想处理同一日期的购买，那么你可以使用max(p。目前为止,p.id)。

在索引方面，我将使用一个关于购买的索引(customer_id，日期，[您想在选择中返回的任何其他购买列])。

LEFT OUTER JOIN(相对于INNER JOIN)将确保从未购买过的客户也包括在内。

我需要你需要的东西，尽管是在许多年后，我尝试了最流行的两个答案。这些都没有产生预期的结果。这就是我所能提供的…为了清晰起见，我更改了一些名称。

SELECT 
  cc.pk_ID AS pk_Customer_ID, 
  cc.Customer_Name AS Customer_Name, 
  IFNULL(pp.pk_ID, '') AS fk_Purchase_ID,
  IFNULL(pp.fk_Customer_ID, '') AS fk_Customer_ID,
  IFNULL(pp.fk_Item_ID, '') AS fk_Item_ID,
  IFNULL(pp.Purchase_Date, '') AS Purchase_Date
FROM customer cc
LEFT JOIN purchase pp ON (
  SELECT zz.pk_ID 
  FROM purchase zz 
  WHERE cc.pk_ID = zz.fk_Customer_ID 
  ORDER BY zz.Purchase_Date DESC LIMIT 1) = pp.pk_ID
ORDER BY cc.pk_ID;

另一种方法是在你的连接条件中使用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
   )
)

先不讲代码，逻辑/算法如下:

Go to the transaction table with multiple records for the same client. Select records of clientID and the latestDate of client's activity using group by clientID and max(transactionDate) select clientID, max(transactionDate) as latestDate from transaction group by clientID inner join the transaction table with the outcome from Step 2, then you will have the full records of the transaction table with only each client's latest record. select * from transaction t inner join ( select clientID, max(transactionDate) as latestDate from transaction group by clientID) d on t.clientID = d.clientID and t.transactionDate = d.latestDate) You can use the result from step 3 to join any table you want to get different results.

在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