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