mysql - Mysql按客户分组并获取最后3条记录

Question

Sql Table               
Sales Date  order Id    customer_id medium  
07-07-2018  WP241530914666620   26  4   
21-07-2018  WP241532133344497   26  4   
29-07-2018  WP241532821589511   26  4   
01-07-2018  1100070191  61  3   
05-07-2018  1100071337  61  3   
11-07-2018  1100073077  61  3   
15-07-2018  1100074754  61  3   
21-07-2018  1100075959  61  3   
25-07-2018  1100077484  61  3   
01-07-2018  100207654   64  3   
07-07-2018  100210077   64  3   
08-07-2018  WP241531004708220   64  4   

Result:-                
Sales Date  order Id    customer_id medium  
07-07-2018  WP241530914666620   26  4   
21-07-2018  WP241532133344497   26  4   
29-07-2018  WP241532821589511   26  4   
01-07-2018  1100070191  61  3   
05-07-2018  1100071337  61  3   
11-07-2018  1100073077  61  3   

我需要 mysql 查询以上结果。

上表中每个客户的最后 3 个订单。我正在尝试一些代码，但无法获得上述结果

标签： mysqlsql

Answer 1

您可以使用相关的子查询，例如：

select Sales_Date,order_Id,customer_id,medium 
from
(select y1.Sales_Date,y1.order_Id,y1.customer_id,y1.medium,
(select count(*) 
from 'yourtable' y2 
where y2.customer_id=y1.customer_id 
and (y2.Sales_Date < y1.Sales_Date or y2.Sales_Date = y1.Sales_Date)
) rn 
from 'yourtable' y1
) finalresult 
where rn<=3

参考：

相关子查询

例子