清洗---sql语句按某字段在指定时间段内去重，并保留id最大的那一条

迈不过友情╰ 2024-04-18 18:22 31阅读 0赞

**清洗—sql语句按某字段在指定时间段内去重，并保留id最大的那一条**

> 1.先学会查找有重复记录的数据，这里按cve\_number是否重复去重。 select cve\_number ，count(cve\_number) from sys\_bug group by cve\_number having  
> count(cve\_number) > 1 这样就可以看到那些数据有重复，并且可以看到有多少条重复的  
> 2.找到重复数据中id最大的那一条 select max(id) as id from sys\_bug group by cve\_number having count(cve\_number)>1  
> 3.IN 使用场景是：in 后面要跟一组数据, 而子查询返回的数据类型和它对应不上, 所以应该将子查询的结果当做一个表, 从其中再将那列数据取出来 select cve\_number from (select cve\_number from sys\_bug  
> group by cve\_number having count(cve\_number) > 1) as table1
> 
> select id from (select max(id) as id from sys\_bug group by cve\_number  
> having count(cve\_number)>1) as table2  
> 4.cve\_number in（xxx）表示cve\_number 在这里面取值。id not in（xxx），表示id不取这里面的数据。  
> 5.注意时间的大于等于的写法：（传入的时间是字符串：格式，2019-09-09）

<if test="startTime != null and startTime != ''">
                AND <![CDATA[open_date >= DATE_FORMAT(#{startTime},'%Y-%m-%d')]]>
            </if>

> 6.终上所述，最终的sql为：

delete from sys_bug
            where
            cve_number in (select cve_number from (select cve_number from sys_bug group by cve_number having count(cve_number) > 1) as table1)
            and
            id not in
            (select  id from (select max(id) as id from sys_bug group by cve_number having count(cve_number)>1) as table2)
    
            <if test="startTime != null and startTime != ''">
                AND <![CDATA[open_date >= DATE_FORMAT(#{startTime},'%Y-%m-%d')]]>
            </if>
            <if test="endTime != null and endTime != ''">
                AND <![CDATA[open_date <= DATE_FORMAT(#{endTime},'%Y-%m-%d')]]>
            </if>