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
)