拉取班主任黄群的学生明细

#### 引用自:
#### 拉取班主任黄群的学生明细
#### 执行时间:36.787s,shape(274,3),新脱敏库hfjy
#### 日期:2018-12-20
#### 需求方:高敏霞
#### 数据输出方式:excel
#### created by:汪国强
#### update by:汪国强
#### checked by:

SELECT
  sd.student_no 学生编号,
  sd.name 学生姓名,
  tb.rest 剩余课时数,
  (case sd.curriculum_status  
    when 0 then '正常学生' 
    when 1 then '已结课' 
    when 2 then '停课' 
    when 3 then '结课预警' 
    when 4 then '停课预警' 
    when 5 then '结课预警,停课预警' 
    end) 停结课情况,
  ui.name 班主任姓名
FROM
  view_student sd
    left join view_user_info ui on ui.user_id = sd.by_assistant
    left join 
  (
  select 
    t.student_intention_id, sum(t.rest) as rest 
  from
    (select 
      c.student_intention_id, c.contract_id, 
      c.period + ifnull(c.donate_period,0)-ifnull(sum(l.class_period),0) as rest, sum(l.class_period)
    from 
      view_tms_contract c 
    left join lesson_plan l on l.contract_id = c.contract_id and l.status<>0 and l.solve_status = 5
    where big_type_id = 1 and c.status in (4,5) and c.teacher_level is not null 
    group by c.contract_id)  t 
  group by t.student_intention_id
  ) tb on tb.student_intention_id = sd.student_intention_id

WHERE
    ui.name = '黄群' and sd.student_no is not null

拉取学生编号 学生姓名

#### 引用自:
#### 拉取拉取学生编号 学生姓名
#### 执行时间:0.416s,shape(274,3),新脱敏库hfjy
#### 日期:2018-12-20
#### 需求方:张明杰
#### 数据输出方式:excel
#### created by:汪国强
#### update by:汪国强
#### checked by:

select
    tc.contract_id 合同编号,
    tc.student_name 学生姓名,
    tc.student_no 学生编号
from
    view_tms_contract tc
where
    tc.contract_id in ('X29011812006367','X20011812001838')
文档更新时间: 2019-01-18 16:35   作者:admin