多个字段相同的重复记录去重

表：TNewSaleAreaProduct

id            SaleAreaCode                 ProductCode  

1                    1.1                                  A1

2                    1.1                                  A2

3                    1.2                                  A1

4                    1.2                                  A1

 

想得到这样的结果

1                    1.1                                  A1

2                    1.1                                  A2

3                    1.2                                  A1

 

id 为 3  4  随便取一条。

 

alter table TNewSaleAreaProduct add flag int                   --添加标示列，为1的为有用的

update TNewSaleAreaProduct set flag=1   from
(select SaleAreaCode as aa,ProductCode as bb  from TNewSaleAreaProduct  group by SaleAreaCode,ProductCode   having count(*) =1)
as c 
where SaleAreaCode=c.aa and ProductCode=c.bb         --只有一条的，保留

update TNewSaleAreaProduct  set flag=1   from
(select min(id) as idid from TNewSaleAreaProduct  group by SaleAreaCode,ProductCode having count(*) >1)
as c 
where id =c.idid                                                                        --多条的保留一条

delete from TNewSaleAreaProduct where flag is null      --删除flag不为1的

 

 

 

 

方法二

delete from table where id not in

(

    select max(id) from table group by storecode

)