假设我有一个顾客表和一个购买表。每笔购买属于一个客户。我想在一个SELECT语句中获得所有客户及其最后一次购买的列表。最佳做法是什么?关于建立索引有什么建议吗?
请在回答中使用这些表/列名:
客户:id，姓名 购买:id, customer_id, item_id，日期
在更复杂的情况下，通过将最后一次购买放入customer表来对数据库进行非规范化(性能方面)是否有益?
如果(purchase) id保证按日期排序，是否可以使用LIMIT 1之类的东西来简化语句?
当前回答
另一种方法是在你的连接条件中使用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
)
)
其他回答
表:
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
)
)
你也可以指定一些条件到子选择查询
另一种方法是在你的连接条件中使用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
)
)
试试这个，会有帮助的。
我在我的项目中使用了这个。
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]
这是经常出现在StackOverflow上的“每组最多n个”问题的一个例子。
以下是我通常建议的解决方法:
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。
有些人使用子查询而不是我上面展示的解决方案，但我发现我的解决方案更容易解决关系。
如果你正在使用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子句中最左边的字段。
注意:这是一个非标准条款。
