请尝尝这个，

SELECT 
c.Id,
c.name,
(SELECT pi.price FROM purchase pi WHERE pi.Id = MAX(p.Id)) AS [LastPurchasePrice]
FROM customer c INNER JOIN purchase p 
ON c.Id = p.customerId 
GROUP BY c.Id,c.name;

试试这个，会有帮助的。

我在我的项目中使用了这个。

SELECT 
*
FROM
customer c
OUTER APPLY(SELECT top 1 * FROM purchase pi 
WHERE pi.customer_id = c.Id order by pi.Id desc) AS [LastPurchasePrice]

请尝尝这个，

SELECT 
c.Id,
c.name,
(SELECT pi.price FROM purchase pi WHERE pi.Id = MAX(p.Id)) AS [LastPurchasePrice]
FROM customer c INNER JOIN purchase p 
ON c.Id = p.customerId 
GROUP BY c.Id,c.name;

在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

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

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.

您还没有指定数据库。如果它是一个允许分析函数的方法，那么使用这种方法可能比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