可以在这两个数据集中加个是否上过体验课字段吗(‘是’,’否’)

#### 主题:可以在这两个数据集中加个是否上过体验课字段吗('是','否') 
#### 执行时间:300.01s,shape(673630,37),新脱敏库和bidata
#### 需求方: 周阳新
#### created_by:汪国强
#### update by:赵淼
#### checkd by:赵淼

select 
        mla.*,
        (case when (select count(*) from lesson_plan where student_id = lp.student_id and lesson_type =3 
and status in (3,5) and solve_status <> 6)>0 then '是' else '否' end) '是否上过体验课',
        case when locate('艺术',concat(ifnull(s.present_school,0),ifnull(otc.content,0),ifnull(lpo.learning_target,0),
    ifnull(lpo.self_evaluation,0),ifnull(lpo.teacher_requirements,0),ifnull(lpo.target_schoolOrmajor,0)))>0 then '艺考'
               when locate('艺考',concat(ifnull(s.present_school,0),ifnull(otc.content,0),ifnull(lpo.learning_target,0),
    ifnull(lpo.self_evaluation,0),ifnull(lpo.teacher_requirements,0),ifnull(lpo.target_schoolOrmajor,0)))>0 then '艺考'
                 when locate('音乐',concat(ifnull(s.present_school,0),ifnull(otc.content,0),ifnull(lpo.learning_target,0),
    ifnull(lpo.self_evaluation,0),ifnull(lpo.teacher_requirements,0),ifnull(lpo.target_schoolOrmajor,0)))>0 then '音乐'
                 when locate('艺体',concat(ifnull(s.present_school,0),ifnull(otc.content,0),ifnull(lpo.learning_target,0),
    ifnull(lpo.self_evaluation,0),ifnull(lpo.teacher_requirements,0),ifnull(lpo.target_schoolOrmajor,0)))>0 then '体育'
                 when locate('体育',concat(ifnull(s.present_school,0),ifnull(otc.content,0),ifnull(lpo.learning_target,0),
    ifnull(lpo.self_evaluation,0),ifnull(lpo.teacher_requirements,0),ifnull(lpo.target_schoolOrmajor,0)))>0 then '体育'
                 when locate('美术',concat(ifnull(s.present_school,0),ifnull(otc.content,0),ifnull(lpo.learning_target,0),
    ifnull(lpo.self_evaluation,0),ifnull(lpo.teacher_requirements,0),ifnull(lpo.target_schoolOrmajor,0)))>0 then '美术'
                 else '正常课程'  end is_normal
from 
        bidata.market_lesson_audio_2  mla
left join lesson_relation lr on lr.plan_id = mla.lesson_plan_id
left join lesson_plan_order lpo on lpo.order_id = lr.order_id
left join order_trial_class otc ON lpo.order_id=otc.order_id
left join lesson_plan lp on lp.lesson_plan_id = mla.lesson_plan_id
left join view_student s on s.student_id = lp.student_id
where  mla.adjust_start_time <= '2019-02-01'
文档更新时间: 2019-03-08 11:44   作者:admin