假设我有一个顾客表和一个购买表。每笔购买属于一个客户。我想在一个SELECT语句中获得所有客户及其最后一次购买的列表。最佳做法是什么?关于建立索引有什么建议吗?
请在回答中使用这些表/列名:
客户:id，姓名 购买:id, customer_id, item_id，日期
在更复杂的情况下，通过将最后一次购买放入customer表来对数据库进行非规范化(性能方面)是否有益?
如果(purchase) id保证按日期排序，是否可以使用LIMIT 1之类的东西来简化语句?
当前回答
您还没有指定数据库。如果它是一个允许分析函数的方法，那么使用这种方法可能比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
其他回答
在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
)
)
你也可以指定一些条件到子选择查询
您还没有指定数据库。如果它是一个允许分析函数的方法，那么使用这种方法可能比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
选择应加入所有客户和他们的最后购买日期。
先不讲代码，逻辑/算法如下:
Go to the transaction table with multiple records for the same client. Select records of clientID and the latestDate of client's activity using group by clientID and max(transactionDate) select clientID, max(transactionDate) as latestDate from transaction group by clientID inner join the transaction table with the outcome from Step 2, then you will have the full records of the transaction table with only each client's latest record. select * from transaction t inner join ( select clientID, max(transactionDate) as latestDate from transaction group by clientID) d on t.clientID = d.clientID and t.transactionDate = d.latestDate) You can use the result from step 3 to join any table you want to get different results.
