假设我有一个顾客表和一个购买表。每笔购买属于一个客户。我想在一个SELECT语句中获得所有客户及其最后一次购买的列表。最佳做法是什么?关于建立索引有什么建议吗?
请在回答中使用这些表/列名:
客户:id，姓名 购买:id, customer_id, item_id，日期
在更复杂的情况下，通过将最后一次购买放入customer表来对数据库进行非规范化(性能方面)是否有益?
如果(purchase) id保证按日期排序，是否可以使用LIMIT 1之类的东西来简化语句?
当前回答
在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
其他回答
请尝尝这个，
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。目前为止,p.id)。
在索引方面，我将使用一个关于购买的索引(customer_id，日期，[您想在选择中返回的任何其他购买列])。
LEFT OUTER JOIN(相对于INNER JOIN)将确保从未购买过的客户也包括在内。
您还没有指定数据库。如果它是一个允许分析函数的方法，那么使用这种方法可能比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
您也可以尝试使用子选择来完成此操作
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
选择应加入所有客户和他们的最后购买日期。
