活跃师生组队-周

#### 主题:活跃师生组队-周
#### 执行时间:xxxs,shape:xxx,新脱敏库
#### 需求方: 王新迎
#### created_by:汪国强
#### update by:
#### checkd by:

SELECT
        #date_format(lp.adjust_start_time,'%Y-%m-%d') '上课年月',
        (SELECT lp.adjust_start_time FROM lesson_plan l WHERE s.student_id = l.student_id ORDER BY lp.adjust_start_time DESC limit 1) '上课年月',
        #lp.adjust_start_time '上课年月',
        s.student_no '学生编号',
        s.name '学生姓名',
        s.exam_year '高考年份',
        t.teacher_no '教师编号',
        (select name from view_user_info u where u.user_id = lp.teacher_id) '教师姓名',
        (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) '教学组'
FROM
        view_student s
LEFT JOIN
        lesson_plan lp on lp.student_id = s.student_id
LEFT JOIN
        view_teacher t on t.teacher_id = lp.teacher_id
LEFT JOIN
        view_tms_teacher_ext tt on tt.id = t.teacher_id
WHERE
        lp.lesson_type =1 -- 课程类型(0:首课、1:正课、2:试听课、3:体验课)
        and lp.status in (3,5) -- 授课计划状态 0 已取消1 待授课 2 授课中 3已授课 4已设置课件 5未用系统 6预排课 7自动排课占课
        and lp.solve_status<>6 -- 操作变更状态 1:初始值;2:老师确认班主任未确认;3:班主任确认老师未确认 4:上报异常 5:管理员确认或老师班主任都确认 6:取消(月初将上月非异常课程翻新为5)
        and lp.adjust_start_time between '2018-12-01' and curdate()#'2018-12-10'
        and t.quarters_type <>5 -- 岗位属性不等于5:老师体验课
GROUP BY
        date_format(lp.adjust_start_time,'%Y-%u'),s.student_no,t.teacher_no

活跃师生组队-月

#### 主题:活跃师生组队-周
#### 执行时间:xxxs,shape:xxx,新脱敏库
#### 需求方: 王新迎
#### created_by:汪国强
#### update by:
#### checkd by:

SELECT
        #date_format(lp.adjust_start_time,'%Y-%m-%d') '上课年月',
        (SELECT lp.adjust_start_time FROM lesson_plan l WHERE s.student_id = l.student_id ORDER BY lp.adjust_start_time DESC limit 1) '上课年月',
        #lp.adjust_start_time '上课年月',
        s.student_no '学生编号',
        s.name '学生姓名',
        s.exam_year '高考年份',
        t.teacher_no '教师编号',
        (select name from view_user_info u where u.user_id = lp.teacher_id) '教师姓名',
        (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) '教学组'
FROM
        view_student s
LEFT JOIN
        lesson_plan lp on lp.student_id = s.student_id
LEFT JOIN
        view_teacher t on t.teacher_id = lp.teacher_id
LEFT JOIN
        view_tms_teacher_ext tt on tt.id = t.teacher_id
WHERE
        lp.lesson_type =1 -- 课程类型(0:首课、1:正课、2:试听课、3:体验课)
        and lp.status in (3,5) -- 授课计划状态 0 已取消1 待授课 2 授课中 3已授课 4已设置课件 5未用系统 6预排课 7自动排课占课
        and lp.solve_status<>6 -- 操作变更状态 1:初始值;2:老师确认班主任未确认;3:班主任确认老师未确认 4:上报异常 5:管理员确认或老师班主任都确认 6:取消(月初将上月非异常课程翻新为5)
        and lp.adjust_start_time between '2018-12-01' and curdate()#'2018-12-10'
        and t.quarters_type <>5 -- 岗位属性不等于5:老师体验课
GROUP BY
        date_format(lp.adjust_start_time,'%Y-%M'),s.student_no,t.teacher_no
文档更新时间: 2019-03-15 11:06   作者:admin