SQL 连接 : selecting the last records in a one-to-many relationship

Question

假设我有一张客户表和一张采购表。每次购买都属于一位客户。我想在一个 SELECT 语句中获取所有客户及其最后一次购买的列表。最佳实践是什么？关于建立索引有什么建议吗？

请在您的答案中使用这些表/列名称:

• 客户:id、姓名
• 购买:id、customer_id、item_id、日期

在更复杂的情况下，通过将最后一次购买放入客户表中来对数据库进行非规范化是否(从性能角度)有益？

如果保证(购买)id 按日期排序，是否可以使用 LIMIT 1 之类的内容来简化语句？

Answer 1

这是 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。

<小时/>

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