weixin_44595630
¥小添添¥
2019-09-11 10:52
采纳率: 9.1%
浏览 397

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

我一条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'

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

3条回答 默认 最新

  • qq_32005115
    涅炎sky 2019-09-11 16:47
    已采纳

    你试试这样 应该可以吧
    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'

    点赞 评论
  • ztx18840831027
    青衣沽酒醉风尘 2019-09-11 10:57

    按照主键group 一下就好,然后对那个不同值的字段取max或者min都行

    点赞 评论
  • qimeimeiqi
    chouchou~ 2019-09-11 16:06

    我今天刚写的oracle去重,不知道对你有没有用。
    DELETE from 表名 WHERE(字段) IN ( SELECT 字段 FROM 表名 GROUP BY 字段 HAVING COUNT(字段)>1)
    AND ROWID NOT IN (SELECT MAX(ROWID) FROM 表名 GROUP BY 字段 HAVING COUNT (*)>1);

    其中,max代表重复行的最底下一条
    如果想保留表格最上面一条,把max换成min

    点赞 评论

相关推荐