扩展内推渠道

#### 引用自:
#### 扩展内推渠道,提高招聘效率
#### 执行时间:0.12s,shape(274,3),不脱敏
#### 日期:2018-12-19
#### 需求方:符仲豪
#### 数据输出方式:excel
#### created by:汪国强
#### update by:汪国强
#### checked by:

SELECT
    t.teacher_no 教师编号,
    case
        t.quarters_type
    when 1 then '全职授课'
    when 2 then '全职教研'
    when 3 then '兼职'
    when 4 then '实习'
    end 职位,
  (select 
      group_concat(subject_name) 
  from 
      subject 
  where 
      subject_id in (
                    select 
                        subject_id
                    from
                        teacher_subject_rel
                    where 
                        teacher_id = t.teacher_id)) 授课科目,
    u.phone 手机号码
FROM
    view_teacher     t
    left join view_user_info u on u.user_id = t.teacher_id
WHERE
    t.teacher_no in('EN986428','MA895758','CM619710','EN973284','MA872422','MA510182','CH638099','MA832436','EN452180','EN605560','MA650143','EN431375','MA623555','CH672780','MA251350','CH279462','MA416408','MA894492','PH773706','MA106630','CH570976','CH351722','CH590160','CM877339','PH761791','CH965963','EN651501','EN406361','CH730883','MA827543','EN136134','CH271984','EN230456','BI562010','CM351760','MA351150','MA695954','CH209523','MA213790','HI462339','MA116751','MA541162','MA187609','MA156942','MA403235','EN362828','CH379783','MA518124','GE266846','CH506465','MA793934','MA329451','MA834375','PH486008','MA967601','MA169318','EN223844','CH234352','CH232263','CM841499','GE239029','CH148363','CH127614','PH732951','CH335506','CH879687','CM679366','MA992383','MA460990','CH935940','MA488461','HI591424','CH810088','PH584001','PH639570','EN155475','PH311187','PH527149','EN917008','CH579165','MA358390','CH458473','PH756906','PH425722','EN377041','MA483267','EN849121','MA583832','MA275023','PO774285','CH456357','MA783656','MA441030','MA920021','MA275992','CH410863','MA547833','BI135382','MA147963','CH381538','MA149169','MA900789','MA580845','PH889762','MA675440','MA289681','EN580408')
    and t.quarters_type <> 5

陈佳的需求

#### 引用自: 赵淼
#### 学员编号   高考年份  最新剩余课时    最近一次消课时间   班主任  班主任所在部门 班主任所在组  6-12月总计消课次数   其中时长1小时的消课次数  
#### 执行时间:0.12s,shape(274,3),不脱敏
#### 日期:2018-12-19
#### 需求方:陈佳
#### 数据输出方式:excel
#### created by:汪国强
#### update by:汪国强
#### checked by:

SELECT
    s.student_no 学员编号,
    s.exam_year 高考年份,
    tb.rest 最新剩余课时,
    max(adjust_start_time) 最后一次上课日期,
    csl.by_assistant 班主任,
    csl.assistant_dept 班主任部门,
    csl.assistant_group 班主任所在组,
    sum(case when lp.adjust_start_time >='2018-06-01'and  lp.adjust_start_time< curdate() then 1 else  0 end) '6-12月总计消课次数',
    sum(case when lp.adjust_start_time >='2018-07-01'and  lp.adjust_start_time<'2018-09-01'  then 1 else  0 end) '7-8月总计消课次数',
    sum(case when lp.adjust_start_time >='2018-06-01'and  lp.adjust_start_time<= curdate() and timestampdiff(SECOND,lp.adjust_start_time,lp.adjust_end_time)/3600=1 then 1 else  0 end ) '6-12月1小时的消课次数',
    sum(case when lp.adjust_start_time >='2018-07-01'and  lp.adjust_start_time<'2018-09-01' and timestampdiff(SECOND,lp.adjust_start_time,lp.adjust_end_time)/3600=1 then 1 else  0 end ) '7-8月1小时的消课次数',
  csl.status 学员当时状态


FROM
    lesson_plan lp
  left join view_student s on lp.student_id = s.student_id
    left join 
    (
     select student_no,status,remaining_period,by_assistant,assistant_dept,assistant_group  from 
    bidata.yxy_curriculum_status_log where stat_date =date_sub(curdate(),interval 1 day)  
    )
  csl on  csl.student_no = s.student_no
    left join 
  (
  select 
    t.student_intention_id, sum(t.rest) as rest 
  from
    (select 
      c.student_intention_id, c.contract_id, 
      c.period + ifnull(c.donate_period,0)-ifnull(sum(l.class_period),0) as rest, sum(l.class_period)
    from 
      view_tms_contract c 
    left join lesson_plan l on l.contract_id = c.contract_id and l.status<>0 and l.solve_status = 5
    where big_type_id = 1 and c.status in (4,5) and c.teacher_level is not null 
    group by c.contract_id)  t 
  group by t.student_intention_id
  ) tb on tb.student_intention_id = s.student_intention_id

WHERE
  lp.adjust_start_time>='2018-06-01'
    and lp.adjust_start_time< curdate()
    and lp.status in (3,5)
    and lp.solve_status <> 6
    and lp.lesson_type = 1
  and    lp.subject_id<>11
    and s.exam_year <= '2022'
    and (select name from view_user_info where user_id=lp.teacher_id) not like '%测试%' 
  and (select name from view_user_info where user_id=lp.teacher_id) not like '%授课支持%'
GROUP BY
    s.student_id
文档更新时间: 2019-01-18 16:35   作者:admin