sql笔记

根据名称分组查询最近时间的SQL

SELECT
        t1.*   
    FROM
        APS_POSITION t1,
        (SELECT
            VIN,
            MAX(TIME) AS TIME 
        FROM
            APS_POSITION 
        GROUP BY
            VIN) T2        
    WHERE
        t1.vin=t2.vin 
        AND t1.time=t2.time;

根据条件分组 删除重复数据 并且保留rowid最小的一行数据

DELETE FROM APS_POSITION A
 WHERE (A.VIN, A.TIME, A.LAT, A.LNG) IN
       (SELECT VIN, TIME, LAT, LNG
          FROM APS_POSITION
         GROUP BY (VIN, TIME, LAT, LNG)
        HAVING COUNT(*) > 1)
   AND ROWID NOT IN 
　　　　(SELECT MIN(ROWID)
              FROM APS_POSITION
        GROUP BY (VIN, TIME, LAT, LNG)
          HAVING COUNT(*) > 1)