MySQL 选择每个客户的最新调查分数

Question

简单来说，我有一个如下所示的表格:

customer_surveys
----------------
id | customer_id | score | date
1  |           1 |     5 | 01-01-2013     
2  |           1 |     6 | 01-02-2013  
3  |           2 |     8 | 01-03-2013   
4  |           2 |     7 | 01-04-2013

我想创建一个如下所示的 View :

customer_survey_scores
----------------------
customer_id | score
1           | 6
2           | 7

该 View 仅选择每个客户的最新调查分数。

我能够通过编写一个复杂的子查询来实现这一点，但需要它尽可能快地执行。

Answer 1

select c.customer_id, c.score
from customer_surveys c
inner join 
(
  select customer_id, max(date) as mdate
  from customer_surveys 
  group by customer_id
) x on x.customer_id = c.customer_id and x.mdate = c.date