（小笔记）（sql）查找重复记录，并删除多余记录

select substr(a,1,4) as 年级,substr(a,5,4) as 专业代码 ,zy.zymc as 专业名称,b as 课程代码,kc.kczwmc as 课程名称 ,c as 重复条数 from
(
	select kcxx.jxjhh as a,kcxx.kcdm as b,count(*) as c from jwxt.jy_pyjhkcxx kcxx
	where kcxx.zt = 1
	group by kcxx.jxjhh,kcxx.kcdm
	having count(*) > 1
)
left join jwxt.sys_zy zy on zy.zydm = substr(a,5,4)
left join jwxt.sys_kc kc on kc.kcdm = b

select kcxx.jxjhh as a,kcxx.kcdm as b,count(*) as c ,max(id) as maxid
from jwxt.jy_pyjhkcxx kcxx 
where  kcxx.zt = 1
group by kcxx.jxjhh,kcxx.kcdm
having count(*) > 1

删除多余记录：

delete from jy_pyjhkcxx kcxx 
where exists(
select 1 from jy_pyjhkcxx b where b.jxjhh = kcxx.jxjhh and b.kcdm = kcxx.kcdm and kcxx.id<b.id
)