分配前是否成单

SELECT
        lp.lesson_plan_id as '课程id',
        (select subject_name from subject where lp.subject_id = subject_id ) as '上课学科',
        t.teacher_name as '老师姓名',
        (select name from view_user_info where user_id = lp.student_id) as '学生姓名',
        t.teacher_no as '老师编号',
        s.student_no as '学生编号',
        t.teaching_department as '教学部',
        t.teaching_group as '教学组',
        t.legion_name as '教学团',
        t.attribution_user as '归属培训师',
        lp.adjust_start_time as '上课时间',
        (case when (select count(contract_id) from view_tms_contract tc 
       where tc.student_intention_id = s.student_intention_id 
       and status in (3,4,5,9) and submit_time>lp.adjust_start_time)>0 then '是' 
       else '否' end) as '是否成单',
      lpdt.coursewareName as '课件名称',
      (case when (select count(lpdc.lessonPlanId) from lesson_plan_dynamic_courseware lpdc
             WHERE lpdc.lessonPlanId = lp.lesson_plan_id)>0 then '是' else '否' end) as '是否使用动态课件'
FROM
        lesson_plan lp
LEFT JOIN
        view_student s on s.student_id = lp.student_id
LEFT JOIN
        bidata.zm_teacher_info t on t.teacher_id = lp.teacher_id
LEFT JOIN
        lesson_plan_dynamic_courseware lpdt on lpdt.lessonPlanId = lp.lesson_plan_id
WHERE
        lp.lesson_type = 2 
        and lp.status in (3,5)
        and lp.solve_status <> 6
        and lp.adjust_start_time >= '2019-07-10'
        and lp.adjust_start_time < '2019-07-23'
        and s.account_type = 1
        and t.account_type = '正式'
GROUP BY
        lp.lesson_plan_id
文档更新时间: 2019-08-19 11:54   作者:admin