Sql查询选择最近的一对多关系中的两个表

I have a one to many relationship table such as customer to activities table as shown below Customer table

我有一对多的关系表,如客户到活动表,如下面的客户表所示

   Id  Name
   1   Customer 1
   2   Customer 2

Customer activities

Id  customer_id activity created

1      1       Login    2017-01-01 10:52:32
2      1       Logout   2017-01-01 11:75:32
3      2       Post     2017-01-02 10:11:10
4      2       LogOut   2017-01-02 09:11:10

Let assume I have this repeated to thousand both customer table and customer activities. How can I write a single sql (using Mysql) to show the most recent record per each of the customer using created date such as having the result of activities below

假设我重复了千位客户表和客户活动。我如何编写单个sql(使用Mysql)来显示每个客户使用创建日期的最新记录,例如将活动结果显示在下面

Id  customer_id activity created

2      1       Logout   2017-01-01 11:75:32
3      2       Post     2017-01-02 10:11:10

example should be something like:

示例应该是这样的:

SELECT * 
FROM customer_activities 
WHERE created IN 
(
    SELECT MAX(created)
    FROM customer_activities 
    GROUP BY customer_id
);

But the above did not return the required result.

但是上面没有返回所需的结果。

Thanks.

1 个解决方案

#1

You may use the subquery with aggregate and join

您可以将子查询与聚合和连接一起使用

select * 
from customer_activities  c
join (
   select customer_id, max(created) max_created
   from customer_activities 
   group by customer_id
) t on c.customer_id = t.customer_id and
       c.created = t.max_created

or in construct

或在构造中

select * 
from customer_activities  c
where (customer_id, created) in (
   select customer_id, max(created) max_created
   from customer_activities 
   group by customer_id
)

#1