需要所有在职教师的姓名+职位(例如谭冬平/小学数学兼职)

#### 日期:2019-01-28
#### 需求方:谭冬平
#### 数据输出方式:excel
#### created by:汪国强

SELECT
    u.name 教师姓名,
    (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  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)  岗位属性
FROM
    view_teacher t
    left join view_user_info u on u.user_id = t.teacher_id
WHERE
    u.account_type = 1 and u.name not like '%测试%'
    and t.suspend = 0

教师信息匹配

#### 日期:2019-01-28
#### 需求方:伦红叶
#### 数据输出方式:excel
#### created by:汪国强

SELECT
    t.teacher_no 教师编号,
    (case when u.status=0 then '禁用' when u.status=1 then '可用' when u.status=2 then '手机已认证' end) 教师账号状态,
    (case when t.suspend=0 and start_time is not null and end_time >curdate() then '阶段性停课' when t.suspend=0 then '开课' when suspend=1 then '永久停课' end) 教师排课状态,
    (case t.quarters_type when 1 then '全职授课' when 2 then '全职教研' when 3 then '兼职' end) 教师岗位属性,
    (case t.account_type when 1 then '正式' when 2 then '测试'  end) 教师账号类型
FROM
    view_teacher t
    left join view_user_info u on u.user_id = t.teacher_id
WHERE
    t.quarters_type <> 4 and t.quarters_type <> 5
    and t.teacher_no in {0}
文档更新时间: 2019-03-08 11:44   作者:admin