同一学生的相同科目只能有一条成绩记录，但是现在路两次成绩便会有两条记录。
select *
from (select jxb.jxbbh,--教学班代码
jbxx.xh,--学号
jbxx.xsbh,--学生编号
jbxx.xm,--姓名
jbxx.sfzjh,--身份证件号
dw.dwdm,--单位代码
dw.dwbzmc,--单位标准名称
bzkzy.zymc,--专业名称
bj.bjmc,--班级名称
kc.kcdm,--课程代码
kc.kcmc,--课程名称
cjxx.sxxn,--首修学年
cjxx.sxxq,--首修学期
cjxx.zcj,--总成绩
case
when cjxx.tsqk = '02' then --tsqk:特殊情况
1
when cjxx.tsqk = '03' then
2
when cjxx.tsqk = '04' then
3
when cjxx.tsqk = '05' then
4
else
0
end bkyy --？
from (select a.jxbbh,
a.xsbh,
a.sxxn,
a.sxxq,
a.kskcdm,--考试课程代码
a.bfzcj zcj,
a.tsqk,
a.ksxzm,--考试性质码
a.zt--状态
from (select cjxx.xsbh,
cjxx.kskcdm,
cjxx.sxxn,
cjxx.sxxq,
cjxx.tsqk,
cjxx.jxbbh,
cjxx.ksxzm,
cjxx.zt,
case
when cjxx.tsqk is not null then
tsqk.tsqkmc--特殊情况
when cjxx.zcjdm is not null then
cjfzdj.fbfzdjmc--非百分制等级名称
else
cjxx.zcj || ''
end as djbfzcj,
case
when cjxx.tsqk is not null then
tsqk.dybfzd --对应百分制点
when cjxx.zcjdm is not null then
cjfzdj.dybfzcj--对应百分制成绩
else
cjxx.zcj
end as bfzcj
from t_cjgl_xscjxx cjxx
left join t_cjgl_fbfzcjdjb cjfzdj
on cjxx.zcjdm = cjfzdj.fbfzdjdm
left join t_cjgl_tsqksz tsqk
on cjxx.tsqk = tsqk.id) a
order by a.sxxn, a.sxxq) cjxx
left join T_XSJBXX_JBXX jbxx
on jbxx.xsbh = cjxx.xsbh
left join t_bzxb_bzkzy bzkzy
on bzkzy.zydm = jbxx.zydm
left join t_bzxb_bj bj
on bj.bjdm = jbxx.bjdm
left join t_pygl_kc kc
on kc.kcdm = cjxx.kskcdm
left join t_bzxb_dw dw
on dw.dwdm = kc.kkdwdm
left join t_pkgl_jxb jxb
on cjxx.jxbbh = jxb.jxbbh
where (cjxx.zcj >= 0.0 and cjxx.zcj <= 59.9 and
jxb.jxblb in (0, 1, 2) or cjxx.tsqk in ('02', '03', '04'))
and cjxx.sxxn = '2017'
and cjxx.sxxq = '01'
and cjxx.ksxzm = '0'
and cjxx.zt = '2'
order by jbxx.xm) a
where a.bkyy != '4'
可以试试用group by 按组排序，这个组就是你想按照哪个数据库的字段，会去掉该数据库字段的重复列
加上distinct试试
应该可以用group by，把查询出来的姓名和考试科目进行分组