3月课时量

SELECT
        count(distinct a.学生id) 学生数量,
        count(a.课程id) as 上课次数,
        sum(a.上课小时数) as 上课小时数,
        sum(a.课时量 * a.课程单价 ) as 消耗总金额
FROM
        (
        SELECT
                lp.student_id as 学生id,
                lp.lesson_plan_id 课程id,
                timestampdiff(minute,lp.adjust_start_time , lp.adjust_end_time)/60 上课小时数,
                tc.contract_unit_price/100 课程单价,
                lp.class_period as 课时量
        FROM
                lesson_plan lp
        LEFT JOIN
                view_student s ON s.student_id = lp.student_id
        LEFT JOIN
                view_user_info ui on ui.user_id = lp.student_id
        LEFT JOIN
                view_tms_contract tc ON tc.contract_id = lp.contract_id
        WHERE
                lp.adjust_start_time >= '2019-03-01'
                and lp.adjust_start_time < '2019-04-01'
                and lp.lesson_type=1
                and lp.subject_id<>11
                and lp.solve_status<>6
                and lp.status in(3,5)
        )a
文档更新时间: 2019-08-19 13:28   作者:admin