该学科已消正式课课时数

import pandas as pd
import pymysql

data_no = pd.read_excel(r'e:\Desktop\新学员编号学科.xlsx')

sql = '''
SELECT
    s.student_no 学员编号,
    SUBSTR(sub.subject_name,3,10) 学科,
    sum(lp.class_period) 消课课时数
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 {0}
group by 
    lp.student_id,SUBSTR(sub.subject_name,3,10)
'''.format(tuple(data_no['学员编号']))

my_conn = pymysql.connect(host="***", port=3306, user="***",passwd="***", db="hfjydb", charset="utf8")

df1 = pd.read_sql(sql,my_conn)

sql2 = '''
SELECT
    s.student_no 学员编号,
    s.exam_year 高考年份
FROM
    view_student s
WHERE
    s.student_no in {0}
'''.format(tuple(data_no['学员编号']))

df2= pd.read_sql(sql2,my_conn)

df_sub = pd.merge(data_no,df1, how = 'left', on = ['学员编号','学科'])
df_sub = pd.merge(df_sub,df2, how = 'left', on = '学员编号')

df_sub.to_excel(r'e:\Desktop\新学员编号学科-----闪耀辉.xlsx',index=False,encoding='GB18030')

伦红叶匹配HRMID

SELECT
        t.teacher_no  教师编号,
        t.email_personal  教师邮箱,
        (select  hrm_teacher_id  from  jx_teacher_hrm_id  where          teacher_id=t.teacher_id)  HRMid
FROM
        view_teacher  t
WHERE
        t.teacher_no  in {0}

佘宇阳匹配教师编号

SELECT
    u.name 姓名,
    u.phone 电话,
    t.teacher_no 教师编号
FROM
    view_teacher t
left join 
    view_user_info u on u.user_id = t.teacher_id
WHERE
    u.phone in {0}

邢林敏匹配手机号

SELECT
    s.student_no 学生编号,
    s.phone 手机号
FROM
    view_student s
WHERE
    s.student_no in {0}

张瑾教师积分兑换

SELECT
    teacherId,
    giftName,
    giftIntegral,
    receiverRealName,
    receiverPhone,
    receiverProvince,
    receiverCity,
    receiverArea,
    receiverAddress,
    createTime
FROM
    teacher_gift_order --正式库integral
WHERE
    createTime  >=  '20190114'and  createTime  <  '20190217'
文档更新时间: 2019-03-08 11:44   作者:admin