SQL removal of duplicate records

There are two meanings of duplicate records, one is a completely duplicate record, that is, all fields are duplicated records, and the second is some key field duplicate records, such as the Name field repeats, and other fields may not be repeated or can be ignored.
1, for the first kind of repetition, easier to solve, using
SELECT DISTINCT * from TableName
You can get a result set with no duplicate records.
If the table needs to delete duplicate records (duplicate records retain 1), you can delete them as follows
SELECT DISTINCT * to #Tmp from TableName
drop table TableName
SELECT * Into TableName from #Tmp
drop table #Tmp
This duplication occurs because the table is poorly designed and the unique index columns are added to resolve.

2, this kind of repetition problem usually requires to keep the first record in the duplicate record, the operation method is as follows
Suppose there is a duplicate field name,address, which requires the result set to be unique for both fields
Select Identity (int,1,1) as Autoid, * into #Tmp from TableName
Select min (autoid) as autoid into #Tmp2 from #Tmp Group by name,address
SELECT * from #Tmp where autoid on (select Autoid from #tmp2)
The last select is the result set that name,address not duplicate (but one more autoid field, which can be written in the SELECT clause without this column in the actual write)
Or
With Dups as
(
???? SELECT *, Row_number () over (partition by Product_code order by Product_code) as RowNum
???? From #prod
)


Delete from dups where rownum > 1;

If you think this article is helpful, don't forget to support it!

The records of their own use of distinct are as follows:

   <!--customer Batch List--  <select id= "Listpagecusbatch" parametertype= "Parammap" resulttype= "PD" >  Select Distinct Tp.post_batchno, Tp.post_time, tp.customer_id, tc.customer_name from      tb_dispatch_mail_post TP    Left JOIN Tb_dispatch_customer tc on tp.customer_id = tc.customer_id   <where>   Tp.post_batchno are not null    & Lt;if test= "Parammap.posttimestart!=null and ParamMap.postTimeStart.length () > 0" > and  to_date (#{ Parammap.posttimestart}, ' Yyyy-mm-dd hh24:mi:ss ') <= tp.post_time  </if>  <if test= " Parammap.posttimeend!=null and ParamMap.postTimeEnd.length () > 0 ">  and To_date (#{parammap.posttimeend}, ' Yyyy-mm-dd hh24:mi:ss ') >= tp.post_time   </if>    </where>  ORDER by Tp.post_batchno DESC  </select>

SQL removal of duplicate records