ACCESS SQL 收集

1、快速查询b表中在a表中不存在的数据（right join 可以查询出具b表的全部数据，如果a.partsno！ = b.partno，则查询的记录为b的数据&a的数据为null
     SELECT b.*
     FROM parts AS a RIGHT JOIN TMCI_GB_PNO AS b ON a.partsno = b.partno
     WHERE a.partsno is null;

2、查找重复项：

    SELECT *
    FROM parts
    WHERE partsno in( SELECT First(parts.partsno) AS [partsno]
    FROM parts
    GROUP BY parts.partsno
    HAVING Count(parts.partsno)>1);

   SELECT a.*
   FROM parts AS a INNER JOIN (   SELECT First(parts.partsno) AS [partsno], Count(parts.partsno) AS NumberOfDups
   FROM parts
   GROUP BY parts.partsno
   HAVING Count(parts.partsno)>1)  AS b ON a.partsno=b.partsno;

3、删除重复项：

    （1）如果是完成相同的记录，没有主键。就添加一列id作为主键，在执行如下语句

     DELETE *
     FROM parts where partsno in  (select partsno from parts group by partsno having count(partsno)>1)  
    and id not in (select max(id) from  parts group by partsno having count(partsno)>1)

     (2)
     不要用这种方式：会产生大量卡迪尔积

    DELETE *
    FROM parts
    WHERE id not in  (select min(id) from parts a  group by a.partsno);