匹配教师编号

#### 引用自:
#### 匹配教师编号
#### 执行时间:0.75,新脱敏库hfjy
#### 日期:2019-01-07
#### 需求方:佘宇阳
#### 数据输出方式:excel
#### created by:汪国强

SELECT
    u.name 教师姓名,
    u.phone 教师电话,
    t.teacher_no 教师编号
FROM
    view_teacher t
    left join view_user_info u on t.teacher_id = u.user_id
WHERE
    u.phone in ('17717544977','13636530972'

匹配教师邮箱、hrmid

#### 引用自:
#### 匹配教师邮箱、hrmid
#### 执行时间:0.75,新脱敏库hfjy
#### 日期:2019-01-07
#### 需求方:伦红叶
#### 数据输出方式:excel
#### created by:汪国强

SELECT
t.teacher_no  教师编号,
t.email_personal  私人邮箱,
hi.hrm_teacher_id  HRMid
FROM
view_teacher  t
left  join  jx_teacher_hrm_id  hi  on  hi.teacher_id  =  t.teacher_id
WHERE
t.teacher_no  in  ('EN257422','EN859804')

12月培训流失教师数

#### 引用自:
#### 12月培训流失教师数
#### 执行时间:0.75,新脱敏库hfjy
#### 日期:2019-01-07
#### 需求方:伦红叶
#### 数据输出方式:excel
#### created by:汪国强

select  
        t.teacher_no  教师编号,
        u.name  教师姓名,
        u.phone  手机号,
        t.teach_qq  qq,
        email_personal  邮箱,
        (select  
        case  teaching_department  when  1  then  '教学一部'  
                when  2  then  '教学二部'
                when  3  then  '教学三部(武汉)'  when  4  then  '教学四部(北京)'
                when  5  then  '教学三部(西安)'  when  6  then  '教学四部(哈尔滨)'  end  
        from  
                view_tms_teacher_ext  where  id=t.teacher_id
        )  教学部,
        (select  
                group_concat(subject_name)  
        from  
                subject  
        where  subject_id  in  
                (select  subject_id  from  teacher_subject_rel  where  teacher_id  =t.teacher_id)
        )  学科,
        (select  group_concat(grade_rank)  from  teacher_grade_rank_rel  where  teacher_id=t.teacher_id)  '授课年级段(小初高)',
        (case  when  t.quarters_type  =1  then  '全职授课'  when  t.quarters_type  =2  then  '全职教研'  when  t.quarters_type  =3  then  '兼职授课'  when  t.quarters_type  =4  then  '实习'  end)  岗位属性,
        (case  when  u.status=0  then  '禁用'  when  u.status=1  then  '可用'  when  u.status=2  then  '手机已认证'  end)  状态,
        entry_time  入培时间,
        case  when  t.is_train=0  then  '培训流失'  end    流失状态,      date(t.update_time)  操作日期,
        (select  name  from  view_user_info  where  user_id=t.update_user)  操作人,
        case  loss_type    when  1  then  '通培流失'  when  2  then  '学科培训流失'  end  流失类型,
        case  
                when  jr.loss_type=1  and  jr.loss_reason_first_level=1  then  '未完成通培考核'  
                when  jr.loss_type=1  and  jr.loss_reason_first_level=2  then  '学历不符'
                when  jr.loss_type=1  and  jr.loss_reason_first_level=3  then  '其他'  
                when  jr.loss_type=2  and  jr.loss_reason_first_level=1  then  '失联'  
                when  jr.loss_type=2  and  jr.loss_reason_first_level=2  then  '未完成单科培训考核卷'  
                when  jr.loss_type=2  and  jr.loss_reason_first_level=3  then  '试讲跳票'  
                when  jr.loss_type=2  and  jr.loss_reason_first_level=4  then  '淘汰'  
                when  jr.loss_type=2  and  jr.loss_reason_first_level=5  then  '主动退出'  
                when  jr.loss_type=2  and  jr.loss_reason_first_level=6  then  '其他'  
        end  流失原因一级,  
        case  
                when  jr.loss_type=1  and  jr.loss_reason_first_level=1  and  jr.loss_reason_second_level=1    then  '失联'  
                when  jr.loss_type=1  and  jr.loss_reason_first_level=1  and  jr.loss_reason_second_level=2  then  '找到其他工作'
                when  jr.loss_type=1  and  jr.loss_reason_first_level=1  and  jr.loss_reason_second_level=3  then  '不认可公司价值观与岗位制度'  
                when  jr.loss_type=1  and  jr.loss_reason_first_level=1  and  jr.loss_reason_second_level=4  then  '其他'
                when  jr.loss_type=2  and  jr.loss_reason_first_level=4  and  jr.loss_reason_second_level=1  then  '学术问题'  
                when  jr.loss_type=2  and  jr.loss_reason_first_level=4  and  jr.loss_reason_second_level=2  then  '培训态度不好'
                when  jr.loss_type=2  and  jr.loss_reason_first_level=4  and  jr.loss_reason_second_level=3  then  '教学风格不合适'  
                when  jr.loss_type=2  and  jr.loss_reason_first_level=4  and  jr.loss_reason_second_level=4  then  '网络问题'
                when  jr.loss_type=2  and  jr.loss_reason_first_level=4  and  jr.loss_reason_second_level=5  then  '其他'  
                when  jr.loss_type=2  and  jr.loss_reason_first_level=5  and  jr.loss_reason _second_level=1 then  '不适应线上教学'
        when jr.loss_type=2 and jr.loss_reason_first_level=5 and jr.loss_reason_second_level=2 then '培训期任务量大' 
        when jr.loss_type=2 and jr.loss_reason_first_level=5 and jr.loss_reason_second_level=3 then '个人、家庭、时间问题'
        when jr.loss_type=2 and jr.loss_reason_first_level=5 and jr.loss_reason_second_level=4 then  '找到新工作'
        when jr.loss_type=2 and jr.loss_reason_first_level=5 and jr.loss_reason_second_level=5 then  '其他'  
    end 流失原因二级,
    jr.remark 流失备注,
    (select hrm_teacher_id from jx_teacher_hrm_id where teacher_id=t.teacher_id) 'hrmID',
    (select uu.name from jx_teacher_hrm_id jx left join view_user_info uu on uu.user_id = jx.hrm_teacher_id where teacher_id=t.teacher_id) '跟进HR'
from 
    view_teacher t 
left join view_user_info u on u.user_id = t.teacher_id 
left join jx_train_loss_remark jr on t.teacher_id=jr.teacher_id
where t.teacher_id in 
    (select t.teacher_id 
    from 
        view_teacher t 
    left join jx_train_loss_remark jr on t.teacher_id=jr.teacher_id
    where is_train=0 and t.quarters_type<>5 and jr.remark is not null
        and t.update_time >= '2018-12-01' and t.update_time < '2019-01-01'
        and (select name from view_user_info where user_id=t.update_user) not like '%测试%'
        and (select name from view_user_info where user_id=t.teacher_id) not like '%测试%'
        and left((select group_concat(subject_name) from subject where subject_id in 
        (select subject_id from teacher_subject_rel where teacher_id =t.teacher_id)),2) not like '升学'
        and (select name from view_user_info where user_id=t.update_user)!=(select name from view_user_info where user_id=t.teacher_id))

##匹配授课中心身份证号码信息

#### 引用自:
#### 匹配授课中心身份证号码信息
#### 执行时间:0.75,新脱敏库hfjy
#### 日期:2019-01-07
#### 需求方:谭冬平
#### 数据输出方式:excel
#### created by:汪国强

sql1:

SELECT
u.name  姓名,
hrm_teacher_id  hrmid,
u.id_number  身份证号
FROM
view_user_info  u
left  join  view_teacher  t  on  t.teacher_id  =  u.user_id
left  join  jx_teacher_hrm_id  thi  on  thi.teacher_id  =  t.teacher_id
WHERE
thi.hrm_teacher_id  in()



sql2:

SELECT
u.name  姓名,
u.id_number  身份证号
FROM
view_user_info  u
WHERE
u.phone  in  ()

匹配入培表格,标记培训进度

#### 引用自:
#### 匹配入培表格,标记培训进度。
#### 执行时间:0.75,新脱敏库hfjy
#### 日期:2019-01-07
#### 需求方:伦红叶
#### 数据输出方式:excel
#### created by:汪国强

select  
    ui.name  教师姓名,
    t.teacher_no  教师编号,
        (select  
                case  when  tte.teaching_department  =  1  then  '教学一部'  when  tte.teaching_department  =  2  then  '教学二部'  
                when  tte.teaching_department  =  3  then  '教学三部(武汉)'  when  tte.teaching_department  =  4  then  '教学四部(北京)'  
                when  tte.teaching_department  =  5  then  '教学三部(西安)'  when  tte.teaching_department  =  6  then  '教学四部(哈尔滨)'  else  '无'  end
          from  view_tms_teacher_ext  tte  where  t.teacher_id  =  tte.id  )  教学部,
          (case  t.quarters_type  when  1  then  '全职授课'  when  2  then'全职教研'  when  3  then'兼职'  when  4  then'实习'  
          when  5  then'体验课老师'  end)岗位属性,t.exit_time  出培日期,  t.storage_time    入库日期,
          t.resource_commit_time    入职资料审核日期,
          hi.hrm_teacher_id  hrmid
from  view_teacher  t  
left  join  jx_teacher_hrm_id  hi  on  hi.teacher_id  =  t.teacher_id
left  join  view_user_info  ui  on  ui.user_id  =  t.teacher_id

where  
        t.exit_time  >=  '2019-01-01'  
        and  t.exit_time  <  '2019-01-07'
        and  ui.account_type  =  1  
        and  ui.name  not  like  '%测试%'
文档更新时间: 2019-03-08 11:44   作者:admin