张瑾需求

#### 日期:2019-01-09
#### 需求方:张瑾
#### 数据输出方式:excel
#### created by:汪国强

select
    lp.lesson_plan_id 课程编号,
    (select sub.subject_name from subject sub where sub.subject_id = lp.subject_id) 课程学科,
    s.name 学生姓名,
    s.student_no 学生编号,
    lp.adjust_start_time 上课开始时间,
    lp.adjust_end_time 上课结束时间,
    ui.name 教师姓名,
    t.teacher_no 教师编号,
    (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) 教学组,
    (select ui.name from view_user_info ui where ui.user_id = t.attribution_user_id) 教师对应培训师,
    (select GROUP_CONCAT(distinct jxle.name) 
        from  jx_legion jxle 
        left join jx_legion_member jxlm  on jxle.id = jxlm.legion_id 
        where jxlm.teacher_id = t.teacher_id
    ) 教师所属团,
    (select group_concat(distinct ui.name) 
    from view_user_info ui
    left join jx_legion jl on jl.leader_teacher_id = ui.user_id
    left join jx_legion_member jlm on jlm.legion_id = jl.id
    where jlm.teacher_id = t.teacher_id
    ) 教师所属团团长
from lesson_plan lp
left join view_user_info ui on lp.teacher_id = ui.user_id
left join view_teacher t on t.teacher_id = lp.teacher_id
left join view_student s on s.student_id = lp.student_id
left join view_tms_teacher_ext tt on lp.teacher_id = tt.id
left join jx_legion jl on jl.leader_teacher_id = ui.user_id
where
    lp.lesson_type = 1
    and lp.status <> 0
    and lp.solve_status <> 6
    and lp.adjust_start_time >= '2019-01-01' and lp.adjust_start_time < '2019-02-01'
    and ui.account_type = 1
    and ui.name not like '%测试%'
文档更新时间: 2019-03-08 11:44   作者:admin