假设我有一个顾客表和一个购买表。每笔购买属于一个客户。我想在一个SELECT语句中获得所有客户及其最后一次购买的列表。最佳做法是什么?关于建立索引有什么建议吗?
请在回答中使用这些表/列名:
客户:id,姓名 购买:id, customer_id, item_id,日期
在更复杂的情况下,通过将最后一次购买放入customer表来对数据库进行非规范化(性能方面)是否有益?
如果(purchase) id保证按日期排序,是否可以使用LIMIT 1之类的东西来简化语句?
假设我有一个顾客表和一个购买表。每笔购买属于一个客户。我想在一个SELECT语句中获得所有客户及其最后一次购买的列表。最佳做法是什么?关于建立索引有什么建议吗?
请在回答中使用这些表/列名:
客户:id,姓名 购买:id, customer_id, item_id,日期
在更复杂的情况下,通过将最后一次购买放入customer表来对数据库进行非规范化(性能方面)是否有益?
如果(purchase) id保证按日期排序,是否可以使用LIMIT 1之类的东西来简化语句?
当前回答
我需要你需要的东西,尽管是在许多年后,我尝试了最流行的两个答案。这些都没有产生预期的结果。这就是我所能提供的…为了清晰起见,我更改了一些名称。
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;
其他回答
试试这个,会有帮助的。
我在我的项目中使用了这个。
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]
请尝尝这个,
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;
我发现这条线索可以解决我的问题。
但当我尝试时,它们的表现很低。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
希望这对你有所帮助。
如果你正在使用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子句中最左边的字段。
注意:这是一个非标准条款。
我需要你需要的东西,尽管是在许多年后,我尝试了最流行的两个答案。这些都没有产生预期的结果。这就是我所能提供的…为了清晰起见,我更改了一些名称。
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;