匹配教师出培等信息

###日期:2019-1-10
###需求方:伦红燕
###需求内容:匹配教师出培等信息
###数据输出方式:excel
###created by:汪国强

SELECT
    t.teacher_no 教师编号,
    u.name 教师姓名,
    (select gd.value from  bidata.gen_dict gd where gd.type ='teaching_department' and gd.code = tt.teaching_department) 教学部,
    (select gd.value from  bidata.gen_dict gd where gd.type ='teaching_group' and gd.code = tt.teaching_group) 学科组,
    (select group_concat(subject_name) from subject where subject_id in (select subject_id from teacher_subject_rel where teacher_id =t.teacher_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.resource_commit_time 资料提交日期,
    t.resource_through_time 资料审核日期,
    jt.send_time 合同发送日期,
    jt.sign_time 合同签署日期,
    jt.seal_time 合同落章日期,
    t.storage_time 入库日期,
    (select name from view_user_info u where u.user_id = t.attribution_user_id) 归属培训师
FROM
    view_teacher t
    left join view_user_info u on u.user_id = t.teacher_id
    left join jx_teacher_contract jt on jt.teacher_id = t.teacher_id
    left join view_tms_teacher_ext tt on t.teacher_id = tt.id
WHERE
    t.exit_time >= '2019-01-01' and t.exit_time < '2019-01-10'
GROUP BY
    t.teacher_no
文档更新时间: 2019-03-08 11:44   作者:admin