跟踪用户,便于统计业绩sql
- -- SELECT
- -- edu_trxorder.id 'id',
- -- edu_trxorder.student_name 'stu_name',
- -- edu_trxorder.mobile 'stu_mob',
- -- edu_course.subject_major_str 'nianji',
- -- user_expand.realname 'tea_name',
- -- user_user.mobile 'tea_mob',
- -- edu_course.join_num 'bm_num',
- -- edu_trxorder.lession_num 'keshi',
- -- edu_trxorder.course_name 'kecheng',
- -- edu_course.course_plan 'kaike_time',
- -- edu_trxorder.pay_time 'tijiao_time'
- -- FROM
- -- edu_trxorder
- -- INNER JOIN edu_course ON edu_trxorder.course_id = edu_course.id
- -- INNER JOIN edu_teacher ON edu_trxorder.teacher_id = edu_teacher.id
- -- INNER JOIN user_user ON user_user.id = edu_teacher.user_id
- -- INNER JOIN user_expand ON user_expand.user_id = user_user.id
- --
- --
- SELECT
- edu_trxorder.id 'id',
- edu_trxorder.student_name 'stu_name',
- t.realname,
- edu_trxorder.mobile 'stu_mob',
- edu_course.subject_major_str 'nianji',
- edu_course.start_time 's_time',
- edu_course.end_time 'e_time',
- edu_trxorder.create_time 'c_time',
- user_expand.realname 'tea_name',
- user_user.mobile 'tea_mob',
- edu_trxorder.amount 'amount',
- edu_trxorder.pay_type 'pay_type',
- edu_trxorder.trx_status 'trx_status',
- edu_course.join_num 'bm_num',
- edu_trxorder.lession_num 'keshi',
- edu_trxorder.course_name 'kecheng',
- edu_course.course_plan 'kaike_time',
- edu_trxorder.pay_time 'tijiao_time'
- FROM
- edu_trxorder,
- (
- SELECT
- edu_trxorder.id AS tid,
- realname
- FROM
- edu_trxorder
- LEFT JOIN edu_code_order ON edu_code_order.order_id = edu_trxorder.request_id
- LEFT JOIN edu_user_promo ON edu_user_promo.promo_code = edu_code_order.code
- LEFT JOIN user_user ON user_user.id = edu_user_promo.user_id
- LEFT JOIN user_expand ON user_user.id = user_expand.user_id
- ) t,
- edu_course,
- edu_teacher,
- user_user,
- user_expand
- WHERE
- edu_trxorder.course_id = edu_course.id
- AND edu_trxorder.teacher_id = edu_teacher.id
- AND user_user.id = edu_teacher.user_id
- AND user_expand.user_id = user_user.id
- AND t.tid = edu_trxorder.id
- AND t.realname IS NOT NULL
来源: https://www.oschina.net/code/snippet_1182885_58609