匹配手机号

#### 日期:2019-01-14
#### 需求方:肖璐
#### 数据输出方式:excel
#### created by:汪国强
SELECT
    t.teacher_no  教师编号,
    u.phone  手机号
FROM
    view_teacher  t
    left  join  view_user_info  u  on  u.user_id  =  t.teacher_id
WHERE
    t.teacher_no  in  ()

芦玉华需求

#### 日期:2019-01-14
#### 需求方:芦玉华
#### 数据输出方式:excel
#### created by:汪国强

select
u.name  '姓名',
group_concat(distinct  s.subject_name)  '学科',
case  ext.teaching_department    when  1  then  '教学一部'  when  2  then  '教学二部'  when  3  then  '教学三部(武汉)'  when  4  then  '教学四部(北京)'    when  5  then  '教学三部(西安)'  when  6  then  '教学四部(哈尔滨)'  end  '教学部',
group_concat(distinct  dg.value)  '年级',
case  t.quarters_type  when  1  then  '全职授课'  when  2  then  '全职教研'    when  3  then  '专职'  when  4  then  '实习'  when  5  then  '体验课老师'  when  0  then  '不限'  end  '职位',
t.entry_time  '入培时间',
tol.operate_date  '出培时间',
t.storage_time  '员工关系审核入库时间',
DATE_ADD(t.storage_time,  INTERVAL  2  MONTH)  '转正时间',
t.storage_time  '首次签订劳动合同时间',
DATE_ADD(t.storage_time,  INTERVAL  1  YEAR)  '合同到期日',
t.storage_time  '试用期开始日期',
DATE_ADD(t.storage_time,  INTERVAL  2  MONTH)  '试用期到期日期',
tp.guaranteed_pay  '转正讲师保底薪资',
t.experience  '教师职级',
CONCAT("'",u.id_number,"'")  '身份证',
u.phone  '个人手机',
t.qq_personal  'QQ',
t.email_personal  '私人邮箱',
concat(ap.area_name  ,  if(RIGHT(ap.area_name,1)='市',  '',  ac.area_name))    '常住地址(省市)',
t.teacher_no  '教师编号'  ,
jtr.hrm_teacher_name  '跟进HR',
jtr.hrm_teacher_id    'HRM_ID',
(select  name  from  view_user_info  where  user_id  =  t.attribution_user_id)  '归属培训师'
from  view_teacher  t  
left  join  jx_teacher_hrm_id  jtr  on  jtr.teacher_id=t.teacher_id
left  join  view_user_info  u  on  u.user_id  =  t.teacher_id
left  join  teacher_subject_rel  tsr  on  tsr.teacher_id  =  t.teacher_id
left  join  subject  s  on  s.subject_id  =  tsr.subject_id
left  join  tms_teacher_grade_rel  tgr  on  tgr.teacher_id  =  t.teacher_id
left  join  hls_ddic  dg  on  dg.code  =  tgr.grade_id  and  dg.type='ST009'
left  join  tms_teacher_payment  tp  on  tp.teacher_id  =  t.teacher_id
left  join  view_tms_teacher_ext  ext  on  ext.id  =  t.teacher_id
left  join  tms_teacher_education  te  on  te.teacher_id  =  t.teacher_id  and  te.is_highest  =  1
left  join  area  ap  on  ap.id  =  (t.area_id  div  10000)*10000
left  join  area  ac  on  ac.id  =  (t.area_id  div  100)*100
left  join  area  ax  on  ac.id  =  t.area_id
left  join  area  ap1  on  ap1.id  =  (ext.bank_card_area_id  div  10000)*10000
left  join  area  ac1  on  ac1.id  =  (ext.bank_card_area_id  div  100)*100
left  join  area  ax1  on  ax1.id  =  ext.bank_card_area_id
left  join  tms_teacher_operate_log  tol  on  tol.teacher_id=t.teacher_id  and  tol.operate_type=4  and  tol.content  like  "%出培%"
where  t.quarters_type  =  1
and  t.is_train=1  and  t.is_perfect>=3
and  tol.operate_date  BETWEEN  '2019-01-07  00:00:00'  and  '2019-01-14  23:59:29'
and  u.name  not  like  "%测试%"
group  by  t.teacher_id
ORDER  BY  tol.operate_date
文档更新时间: 2019-03-08 11:44   作者:admin