地址

SELECT
        lp.lesson_plan_id 课程ID,
        lp.adjust_start_time 日期,
        (CASE lp.lesson_type WHEN 1 THEN '正式课' WHEN 2 THEN '体验课' ELSE '' END) 课程类型,
        sb.subject_name 学科学段,
        s.student_no 学生编号,
        nyd.prov_name 省,
        nyd.city_name 市,
        nyd.dist_name 区
FROM
        lesson_plan lp
LEFT JOIN
        subject sb ON sb.subject_id = lp.subject_id
LEFT JOIN
        view_student s ON s.student_id = lp.student_id
LEFT JOIN
        bidata.nyd_district_detail nyd on nyd.dist_id=s.area_id
WHERE
        lp.lesson_type in (1,2)
        and lp.status in (3,5)
        and lp.solve_status <>6
        and lp.adjust_start_time >= '2019-05-01'
        and lp.adjust_start_time <'2019-06-18'
GROUP BY
        s.student_no,sb.subject_name,lp.lesson_type
SELECT
        lpo.order_id '试听课设班单id',
        lp.adjust_start_time '课程开始时间',
        lp.adjust_end_time '课程结束时间',
        s.student_no '学生编号',
        t.teacher_no '教师编号',
        t.teacher_name '教师姓名',
        LEFT(sb.subject_name,2) '年级',
        sb.subject_name '学科'
FROM
        lesson_plan_order lpo
LEFT JOIN
        lesson_relation lr ON lr.order_id = lpo.order_id
LEFT JOIN
        lesson_plan lp ON lp.lesson_plan_id = lr.plan_id
LEFT JOIN
        subject sb ON sb.subject_id = lp.subject_id
LEFT JOIN
        view_Student s ON s.student_id = lp.student_id
LEFT JOIN
        bidata.zm_teacher_info t On t.teacher_id = lp.teacher_id
WHERE
        lpo.type = 2
        and lp.adjust_start_time >= '2018-04-01'
        and lp.adjust_start_time < '2018-12-01'
        and lp.lesson_type = 2

邮箱组织架构

#!/usr/bin/env python
# coding: utf-8

# In[1]:


import pandas as pd
import pymysql


# In[2]:


conn = pymysql.connect(host="rm-2ze974348wa9e1ev3uo.mysql.rds.aliyuncs.com",port=3306,user="yanyanglong_read",passwd="jZYhtZNwD6kWbCiJlNBI",db="bidata",charset="utf8")


# In[3]:


data = pd.read_excel(r'e:\Desktop\1111\dd.xlsx')
data.head()


# In[4]:


data.dtypes


# In[ ]:


sql = '''
SELECT
        name 员工姓名,
        job_number 员工编号,
        department_name 部门名称,
        department_len 所处部门组织架构第几级,
        first_level,
        second_level,
        third_level,
        fourth_level,
        fifth_level,
        sixth_level,
        city 城市,
        branch 分支,
        center 中心,
        department 部门,
        grp 组
FROM
        will_user_list_crm
WHERE
        job_number in {0}
GROUP BY
        job_number
'''.format(tuple(data['员工编号']))


# In[6]:


df1 = pd.read_sql(sql,conn)

df1.head()


# In[8]:


df_sub = pd.merge(data,df1,how='left',on=['员工编号'])

df_sub.head()


# In[10]:


df_sub.to_csv(r'E:/Desktop/JF_0620_01.csv',encoding='GB18030')

文档更新时间: 2019-08-19 11:39   作者:admin