SQL连接：选择一对多关系中的最后记录-Java 学习之路

217

假设我有一张顾客表和一张购买表 . 每次购买都属于一个客户 . 我想在一个SELECT语句中获取所有客户的列表以及他们上次购买的列表 . 什么是最佳做法？有关构建索引的建议吗？

请在答案中使用这些表/列名称：

customer：id，name
购买：id，customer_id，item_id，日期

在更复杂的情况下，通过将最后一次购买放入客户表中，是否（性能方面）有利于非规范化数据库？

如果（购买）ID保证按日期排序，是否可以通过使用 LIMIT 1 之类的内容简化语句？

9 回答

试试这个，这会有所帮助 .

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

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

回复于 2024-05-06T03:40:44+08:00

99
在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，date，[您要在选择中返回的任何其他购买列]） .

LEFT OUTER JOIN （与 INNER JOIN 相对）将确保包括从未进行过购买的客户 .
回复于 2024-05-06T03:40:44+08:00

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

回复于 2024-05-06T03:40:44+08:00

345
您尚未指定数据库 . 如果它是允许分析函数的那个，那么使用这种方法可能比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
```
回复于 2024-05-06T03:40:44+08:00

请试试这个，

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;

回复于 2024-05-06T03:40:44+08:00

17
如果你正在使用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 子句中最左侧的字段匹配 .

警告：这是一个非标准条款 .
回复于 2024-05-06T03:40:44+08:00
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的功能来分析优化计划 . 例如 . EXPLAIN 在MySQL上 .

有些人使用子查询而不是我上面显示的解决方案，但我发现我的解决方案可以更容易地解决关系 .
回复于 2024-05-06T03:40:44+08:00

您也可以尝试使用子选择执行此操作

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

选择应加入所有客户及其 Last 购买日期 .

回复于 2024-05-06T03:40:44+08:00

我发现这个线程是我问题的解决方案 .

但是当我尝试它们时，性能很低 . 贝娄是我建议更好的表现 .

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

希望这会有所帮助 .

回复于 2024-05-06T03:40:44+08:00

SQL连接：选择一对多关系中的最后记录

9 回答

相关问题