sql数据去重问题，就因为一个字段有不同值

Question

我一条sql中查询出来的数据因为某个字段有不同值，所以整个结果都有重复数据了，现在想只保留一条，不同值那个字段任意保留一条即可。
sql如下：有不同值的字段是这个 rra.assess_status。 求大神帮助

select distinct rm.*,
xt1.staff_name recruitBpName ,
xt2.staff_name recommendPerson1Name,
xt3.staff_name recommendPerson2Name,
rf.file_name fileName,
rra.assess_status,
org.job_name applyPositionName
from recruit_resume_msg rm
left join xt_user_baseinfo xt1
on rm.recruit_bp = xt1.staff_id
left join xt_user_baseinfo xt2
on rm.recommend_person1 = xt2.staff_id
left join xt_user_baseinfo xt3
on rm.recommend_person2 = xt3.staff_id
left join recruit_file_path_msg rf
on rm.file_id = rf.id
left join org_job org
on to_char(rm.apply_position) = to_char(org.id)
left join recruit_person_msg rp
on rm.id = rp.resume_id
left join recruit_resume_assess rra
on rm.id = rra.resume_id
where rp.person_type = '2'

Answer 1

你试试这样 应该可以吧
select distinct rm.*,
xt1.staff_name recruitBpName ,
xt2.staff_name recommendPerson1Name,
xt3.staff_name recommendPerson2Name,
rf.file_name fileName,
(select rra.assess_status from recruit_resume_assess where rm.id = rra.resume_id and rownum = 1 ) as assess_status,
org.job_name applyPositionName
from recruit_resume_msg rm
left join xt_user_baseinfo xt1
on rm.recruit_bp = xt1.staff_id
left join xt_user_baseinfo xt2
on rm.recommend_person1 = xt2.staff_id
left join xt_user_baseinfo xt3
on rm.recommend_person2 = xt3.staff_id
left join recruit_file_path_msg rf
on rm.file_id = rf.id
left join org_job org
on to_char(rm.apply_position) = to_char(org.id)
left join recruit_person_msg rp
on rm.id = rp.resume_id
where rp.person_type = '2'