课程备课时长

SELECT
        b.*
FROM
        (
        SELECT
                a.*
        FROM
                (
                SELECT
                        s.student_no 学生编号,
                        SUBSTR(sub.subject_name,3,10) 学科,
                        lp.class_period as '课时数',
                        lp.adjust_start_time '上课时间',
                        lp.lesson_plan_id,
                        lp.teacher_id
                FROM
                        lesson_plan lp
                left join 
                        view_student s on s.student_id = lp.student_id
                left join
                        subject sub on sub.subject_id = lp.subject_id
                WHERE
                        lp.lesson_type = 1 and lp.status in (3,5) and solve_status<>6 and s.student_no in ('99997834','99999990') and SUBSTR(sub.subject_name,3,10) in ('数学','语文')
                        and lp.adjust_start_time >='2019-03-01' and lp.adjust_start_time < '2019-06-01'
                GROUP BY 
                        学生编号,学科,lp.teacher_id
                ) a
        ORDER BY
                a.课时数 DESC,a.上课时间 DESC
        ) b
GROUP BY
        b.学生编号,b.学科

#>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

#课程ID:11942745
#time全部加一起除6w是分钟
SELECT
        a.teacher_id,
        sum(a.备课时长)/count(a.lesson_plan_id) '平均备课时长'
FROM
(
SELECT
        lp.lesson_plan_id,
        lp.teacher_id,
        udt.time '备课时长',
        udt.type
FROM
        lesson_plan lp
LEFT JOIN
        userdoingtime udt ON udt.sourceId = lp.lesson_plan_id
WHERE
        lp.lesson_plan_id in ('9200475','9200476','9200478','9200479','9200480','9200481') and udt.type = 'LESSON'
GROUP BY
        lp.lesson_plan_id
) a
GROUP BY
        a.teacher_id

SELECT
        lp.lesson_plan_id,
        s.student_no,
        lp.teacher_id,
        SUBSTR(sub.subject_name,3,10) 学科
FROM
        lesson_plan lp
LEFT JOIN
        view_student s On s.student_id = lp.student_id
LEFT JOIN
        subject sub on sub.subject_id = lp.subject_id
WHERE
        lp.lesson_type = 1 and lp.status in (3,5) and solve_status<>6 and s.student_no in (15059946)
        and lp.teacher_id in (1070873) and SUBSTR(sub.subject_name,3,10) in ('英语')
        and lp.adjust_start_time >='2019-03-01' and lp.adjust_start_time < '2019-06-01'
文档更新时间: 2019-08-19 11:45   作者:admin