sql - 如何在访问表中获取不同活动的最后日期？

Question

我想得到每项服务的最后日期。我想实现以下目标：

+-------------------+---------------------+------+------------+---------------+---------------+
|        Vin        |  Concesionario_Crm  |  OTS |   Fecha    |     Service   | Concesionario |
+-------------------+---------------------+------+------------+---------------+---------------+
| JAANLR55EE7102074 |   AUTOCONSA UIO     |   9  | 23/04/2019 | MANTENIMIENTO |  AUTOCONSA UIO|
+-------------------+---------------------+------+------------+---------------+---------------+
| JAANLR55EE7102074 |   AUTOCONSA UIO     |   9  | 10/09/2018 |    MECANICA   |  AUTOCONSA UIO|
+-------------------+---------------------+------+------------+---------------+---------------+

我试过了，但它只是在所选服务的最后日期时获取记录

Mantenimiento = "
    INSERT INTO SERVICIO 
    SELECT DISTINCT 
        t2.Mes_CRM, 
        t2.Razón_social, 
        t2.vin, 
        t2.concesionario_crm, 
        t1.ots,    
        t1.fecha, 
        t2.tipo_servicio, 
        t2.concesionario 
    FROM 
        (
            SELECT 
                MAX(t.Mes_CRM) AS Mes_CRM, 
                t.vin, 
                SUM(t.ots) AS ots, 
                MAX(t.fecha_salida) AS fecha 
            FROM CASO AS t 
            GROUP BY t.vin
        ) AS t1 
        INNER JOIN CASO AS t2 
            ON (t1.Mes_CRM = t2.Mes_CRM) 
            AND (t1.fecha = t2.fecha_salida) 
            AND (t1.vin = t2.vin) 
    WHERE (t2.tipo_servicio = 'MANTENIMIENTO')"

标签： sqlvbams-access

Answer 1

我想你想要：

select c.*
from caso as c
where c.fecha = (select max(c2.fecha)
                 from caso c2
                 where c2.service = c.service
                );