#### 引用自:王涤非
#### 匹配正式课课量
#### 日期:2018-12-12
#### 需求方:黄露
#### 数据输出方式:excel
#### created by:汪国强
#### update by:汪国强
#### checked by: 

select t.teacher_no 编号,(select name from view_user_info where user_id = t.teacher_id) 姓名,
(select case when quarters_type = 1 then '全职授课' when quarters_type = 2 then'全职教研' when quarters_type = 3 then'兼职' when quarters_type = 4 then'实习'
when quarters_type = 5 then'体验课老师' end from view_teacher where teacher_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 sum(timestampdiff(minute,adjust_start_time,adjust_end_time))/60 from lesson_plan where teacher_id=t.teacher_id and status <> 0 and solve_status <> 6 and date(adjust_start_time)
between '2017-01-01' and '2017-01-31') '17-01课时量',
(select sum(timestampdiff(minute,adjust_start_time,adjust_end_time))/60 from lesson_plan where teacher_id=t.teacher_id and status <> 0 and solve_status <> 6 and date(adjust_start_time)
between '2017-02-01' and '2017-02-28') '17-02课时量',
(select sum(timestampdiff(minute,adjust_start_time,adjust_end_time))/60 from lesson_plan where teacher_id=t.teacher_id and status <> 0 and solve_status <> 6 and date(adjust_start_time)
between '2017-03-01' and '2017-03-31') '17-03课时量',
(select sum(timestampdiff(minute,adjust_start_time,adjust_end_time))/60 from lesson_plan where teacher_id=t.teacher_id and status <> 0 and solve_status <> 6 and date(adjust_start_time)
between '2017-04-01' and '2017-04-30') '17-04课时量',
(select sum(timestampdiff(minute,adjust_start_time,adjust_end_time))/60 from lesson_plan where teacher_id=t.teacher_id and status <> 0 and solve_status <> 6 and date(adjust_start_time)
between '2017-05-01' and '2017-05-31') '17-05课时量',
(select sum(timestampdiff(minute,adjust_start_time,adjust_end_time))/60 from lesson_plan where teacher_id=t.teacher_id and status <> 0 and solve_status <> 6 and date(adjust_start_time)
between '2017-06-01' and '2017-06-30') '17-06课时量',
(select sum(timestampdiff(minute,adjust_start_time,adjust_end_time))/60 from lesson_plan where teacher_id=t.teacher_id and status <> 0 and solve_status <> 6 and date(adjust_start_time)
between '2017-07-01' and '2017-07-31') '17-07课时量',
(select sum(timestampdiff(minute,adjust_start_time,adjust_end_time))/60 from lesson_plan where teacher_id=t.teacher_id and status <>  0 and solve_status <> 6 and date(adjust_start_time)
between '2017-08-01' and '2017-08-31') '17-08课时量',
(select sum(timestampdiff(minute,adjust_start_time,adjust_end_time))/60 from lesson_plan where teacher_id=t.teacher_id and status <> 0 and solve_status <> 6 and date(adjust_start_time)
between '2017-09-01' and '2017-09-30') '17-09课时量',
(select sum(timestampdiff(minute,adjust_start_time,adjust_end_time))/60 from lesson_plan where teacher_id=t.teacher_id and status <> 0 and solve_status <> 6 and date(adjust_start_time)
between '2017-10-01' and '2017-10-31') '17-10课时量',
(select sum(timestampdiff(minute,adjust_start_time,adjust_end_time))/60 from lesson_plan where teacher_id=t.teacher_id and status <> 0 and solve_status <> 6 and date(adjust_start_time)
between '2017-11-01' and '2017-11-30') '17-11课时量',
(select sum(timestampdiff(minute,adjust_start_time,adjust_end_time))/60 from lesson_plan where teacher_id=t.teacher_id and status <> 0 and solve_status <> 6 and date(adjust_start_time)
between '2017-12-01' and '2017-12-31') '17-12课时量',
(select sum(timestampdiff(minute,adjust_start_time,adjust_end_time))/60 from lesson_plan where teacher_id=t.teacher_id and status <> 0 and solve_status <> 6 and date(adjust_start_time)
between '2018-01-01' and '2018-01-31') '18-01课时量',
(select sum(timestampdiff(minute,adjust_start_time,adjust_end_time))/60 from lesson_plan where teacher_id=t.teacher_id and status <> 0 and solve_status <> 6 and date(adjust_start_time)
between '2018-02-01' and '2018-02-28') '18-02课时量',
(select sum(timestampdiff(minute,adjust_start_time,adjust_end_time))/60 from lesson_plan where teacher_id=t.teacher_id and status <> 0 and solve_status <> 6 and date(adjust_start_time)
between '2018-03-01' and '2018-03-31') '18-03课时量',
(select sum(timestampdiff(minute,adjust_start_time,adjust_end_time))/60 from lesson_plan where teacher_id=t.teacher_id and status <> 0 and solve_status <> 6 and date(adjust_start_time)
between '2018-04-01' and '2018-04-30') '18-04课时量',
(select sum(timestampdiff(minute,adjust_start_time,adjust_end_time))/60 from lesson_plan where teacher_id=t.teacher_id and status <> 0 and solve_status <> 6 and date(adjust_start_time)
between '2018-05-01' and '2018-05-31') '18-05课时量',
(select sum(timestampdiff(minute,adjust_start_time,adjust_end_time))/60 from lesson_plan where teacher_id=t.teacher_id and status <> 0 and solve_status <> 6 and date(adjust_start_time)
between '2018-06-01' and '2018-06-30') '18-06课时量',
(select sum(timestampdiff(minute,adjust_start_time,adjust_end_time))/60 from lesson_plan where teacher_id=t.teacher_id and status <> 0 and solve_status <> 6 and date(adjust_start_time)
between '2018-07-01' and '2018-07-31') '18-07课时量',
(select sum(timestampdiff(minute,adjust_start_time,adjust_end_time))/60 from lesson_plan where teacher_id=t.teacher_id and status <> 0 and solve_status <> 6 and date(adjust_start_time)
between '2018-08-01' and '2018-08-31') '18-08课时量',
(select sum(timestampdiff(minute,adjust_start_time,adjust_end_time))/60 from lesson_plan where teacher_id=t.teacher_id and status <> 0 and solve_status <> 6 and date(adjust_start_time)
between '2018-09-01' and '2018-09-30') '18-09课时量',
(select sum(timestampdiff(minute,adjust_start_time,adjust_end_time))/60 from lesson_plan where teacher_id=t.teacher_id and status <> 0 and solve_status <> 6 and date(adjust_start_time)
between '2018-10-01' and '2018-10-31') '18-10课时量',
(select sum(timestampdiff(minute,adjust_start_time,adjust_end_time))/60 from lesson_plan where teacher_id=t.teacher_id and status <> 0 and solve_status <> 6 and date(adjust_start_time)
between '2018-11-01' and '2018-11-30') '18-11课时量'
from view_teacher t where  quarters_type <> 5 and suspend = 0
文档更新时间: 2019-01-16 16:57   作者:admin