跟踪用户，便于统计业绩sql

跳至 [1] [全屏预览]

-- 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