MYSQL如何按最后日期为每个客户获取单行策略

Question

i have two table

vehicle_data

 id chassis name      number
  1 00001   customer1 123456
  2 00002   customer2 654321
  3 00003   customer3 645421

insurance_data

 id chassis policy_number expiry_date
  1 00001           11111 22-01-2015
  2 00002           22222 22-01-2015
  3 00003           33333 22-01-2015
  4 00001           44444 22-01-2016
  5 00002           55555 22-01-2017
  6 00001           66666 22-01-2017

what i want

 chassis name      policy_number expiry_date
   00001 customer1         66666 22-01-2017
   00002 customer2         55555 22-01-2017
   00003 customer3         33333 22-01-2015

Answer 1

I think this is what you intend:

select id.*
from insurance_data id
where id.expiry_date = (select max(id2.expiry_date)
                        from insurance_data id2
                        where id.name = id2.name and
                              month(id2.expiry_date) = 1 
                       );

This returns the most recent row in the table for each name for the given month of expiry.