符仲豪需求

#### 主题:符仲豪需求
#### 执行时间:xxxs,shape:xxx,新脱敏库
#### 需求方: 符仲豪
#### created_by:汪国强
#### update by:
#### checkd by:

SELECT
    sk.resumeId,
    sk.resumeName '简历姓名',
    sk.resumePhone '简历手机号',
    sk.resumeMail '简历邮箱',
    sk.resumeSendTime '简历投递日期',
    sk.resumeSchool '毕业学校',
    sk.resumeNowCompany '目前所在公司',
    sk.resumeNowPosition '目前岗位',
    sk.resumeInfoAppendix '候选人简历地址',
    sk.resumeDesc '备注,其他描述',
    sk.resumeAddTime '简历添加时间',
    sk.resumeUpTime '简历最近修改时间',
    sk.resumeNextLinkUpTime '下次沟通时间',
    sk.resumeFirstChannelName '一级渠道名称',
    sk.resumeSecondChannelName '二级渠道名称',
    sk.resumeFirstDeptName '一级部门名称',
    sk.resumeSecondDeptName '二级部门名称',
    sk.resumeThreeDeptName '三级部门名称',
    sk.resumeDirectorName '简历跟进人姓名',
    sk.resumeExpectPositionName '应聘岗位名称',
    sk.resumeEducationName '学历名称',
    (select min(linkUpAddTime) from skill_linkup_record sl where sl.linkUpResumeId = sk.resumeId ) '沟通时间',
    (select max(linkUpViewTime) from skill_linkup_record sl where sl.linkUpResumeId = sk.resumeId ) '面试时间',
    (select max(linkUpEntryTime) from skill_linkup_record sl where sl.linkUpResumeId = sk.resumeId ) '入职时间',
    (select max(linkUpOfferTime) from skill_linkup_record sl where sl.linkUpResumeId = sk.resumeId ) '发送offer时间',
    (case (select outreasonpid from skill_out_reason where outreasonid in (select sor.outreasonpid from skill_out_reason sor  where sor.outreasonid = aa.linkupoutreason))when 1 then '电话邀约' when 11 then '面试淘汰' when 28 then '面试通过' end) '一级淘汰原因',
    (select outreasondesc from skill_out_reason where outreasonid in (select sor.outreasonpid from skill_out_reason sor  where sor.outreasonid = aa.linkupoutreason)) '二级淘汰原因',
    (select sor.outreasondesc from skill_out_reason sor where sor.outreasonid = aa.linkupoutreason) '三级淘汰原因',
    (case sk.resumeEducation  when  1 then '专科' when  2 then '本科'  when 3 then '硕士'  when 4 then '博士及以上' when  5 then '专科及以下' else sk.resumeEducation  end) '学历',
    (case sk.resumeType when 1 then '公开中'  when 2 then '跟进中' when 3 then '黑名单' else  sk.resumeType end) '一级状态',
    (case sk.resumeStatus when 1 then '待处理' when 2 then '未接通' when 3 then '面试安排' when 4 then '面试通过' 
     when 5 then '面试未到场' when 6 then '待定' when 7 then '待发offer' when 8 then '待入职' when 9 then '已入职' when 10 then '淘汰' end) '二级状态'
FROM
    view_skill_resume sk
LEFT JOIN
    (select *
from (
SELECT linkUpResumeId,linkUpAddTime ,linkupoutreason FROM skill_linkup_record  
order by 1,2 desc) a
group by  linkUpResumeId) aa on aa.linkUpResumeId = sk.resumeId
WHERE
    sk.resumeName not like '%测试%'
文档更新时间: 2019-03-14 11:23   作者:admin