欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  php教程

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

程序员文章站 2022-04-10 12:49:46
...
跳至 [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