SQL联接:选择一对多关系中的最后一条记录

假设我有一个顾客表和一个购买表。每笔购买属于一个客户。我想在一个SELECT语句中获得所有客户的列表以及他们的最后一次购买。最佳做法是什么?关于建立索引有什么建议吗?

请在回答中使用这些表/列名:

  • 客户:id， name
  • 购买:id， customer_id， item_id， date

在更复杂的情况下，通过将最后一次购买放入customer表来对数据库进行非规范化(性能方面)是否有益?

如果(purchase) id保证按日期排序，语句是否可以通过使用类似LIMIT 1的东西来简化?

351045 次浏览
小开
最佳答案

这是在StackOverflow上经常出现的greatest-n-per-group问题的一个例子。

以下是我通常建议的解决方法:

SELECT c.*, p1.*
FROM customer c
JOIN purchase p1 ON (c.id = p1.customer_id)
LEFT OUTER JOIN purchase p2 ON (c.id = p2.customer_id AND
(p1.date < p2.date OR (p1.date = p2.date AND p1.id < p2.id)))
WHERE p2.id IS NULL;

解释:给定一行p1，不应该有具有相同客户和较晚日期的行p2(或者在平局的情况下，较晚的id)。当我们发现这是正确的，那么p1是该客户最近的购买。

关于索引，我将在purchase中对列(customer_id， date， id)创建一个复合索引。这可能允许使用覆盖索引来完成外部连接。请确保在您的平台上进行测试，因为优化是依赖于实现的。使用RDBMS的特性来分析优化计划。例如:MySQL上的EXPLAIN。

有些人使用子查询而不是我上面展示的解决方案，但我发现我的解决方案更容易解决关系。

小开

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

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

select应该连接所有客户和他们的最后的购买日期。

小开

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

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

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

但当我尝试时，它们的表现很低。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

希望这对你有所帮助。

小开

请尝尝这个，

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]
小开

在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.date, p.id)。

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

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

小开

如果你正在使用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子句中最左边的字段。

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

小开

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

  1. 到具有相同client的多条记录的transaction表。

  2. 使用group by clientID和max(transactionDate)选择客户活动的clientID和latestDate记录

       select clientID, max(transactionDate) as latestDate
from transaction
group by clientID

  3. inner join带有步骤2结果的transaction表，那么你将拥有transaction表的完整记录，其中只有每个客户端的最新记录。

       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)
  4. 你可以使用步骤3的结果来连接任何你想要得到不同结果的表。

小开

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

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;
小开

在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

小开

表:

Customer => id, name
Purchase => id, customer_id, item_id, date

查询:

SELECT C.id, C.name, P.id, P.date
FROM customer AS C
LEFT JOIN purchase AS P ON
(
P.customer_id = C.id
AND P.id IN (
SELECT MAX(PP.id) FROM purchase AS PP GROUP BY PP.customer_id
)
)

你也可以在sub select查询中指定一些条件


提交答案