sql删除重复数据,保留一条
原文参考:https://www.cnblogs.com/tracer-dhy/p/10664327.html
按stIdCard,stYear,semester划分,删除每个学生每学年的重复数据,并保留rowid最小的一条记录(也可以是其他条件的一条记录)
delete
from a_h_mid b
where b.stu_id in
(select a.stu_id from
(select stName, stu_id,rowid, stIdCard,stYear,semester
from a_h_mid
where (stIdCard,stYear,semester) in
(select stIdCard,stYear,semester
from a_h_mid
group by stIdCard,stYear,semester
having count(*) > 1)
and rowid not in
(select min(rowid)
from ( a_h_mid )
group by stIdCard,stYear,semester
having count(*) > 1) ) a )
一、a_h_mid为源数据表,记录的是学生每学期的成绩,但个学期可能会异常产生多条数据,比如一学期有两条语文成绩,所以需要去重操作。
二、按学生证件号stIdCard、学年stYear、学期semester三个字段为依据,查看是否有重复记录。
select stIdCard,stYear,semester from a_h_mid group by stIdCard,stYear,semester having count(*) > 1
三、保留想要的数据,这里是随便保留了一个rowid最小的记录,可以保留分数最大的或者其他的
select min(rowid) from ( a_h_mid ) group by stIdCard,stYear,semester having count(*) > 1
四、删掉重复的保留最小rowid的记录
delete from a_h_mid b where b.stu_id in (select a.stu_id from (select stName, stu_id,rowid, stIdCard,stYear,semester from a_h_mid where (stIdCard,stYear,semester) in (select stIdCard,stYear,semester from a_h_mid group by stIdCard,stYear,semester having count(*) > 1) and rowid not in (select min(rowid) from ( a_h_mid ) group by stIdCard,stYear,semester having count(*) > 1) ) a )