SQLsummary/活动/工作室报单业绩统计.sql

SET @DATE_START = '2021-06-01';
SET @DATE_STOP = '2021-07-08';
-- DATE_STOP 实际为结束日期-1天

SELECT
	A.`会员编号`,
	U.REAL_NAME AS 会员姓名,
	D.LEVEL_NAME AS 会员级别,
	A.`创建时间`,
	A.`报单PV`,
	U2.USER_NAME AS 推荐人编号,
	U2.REAL_NAME AS 推荐人姓名,
	DZ.`店号`,
	A.`店长编号`,
	DZ.`店长姓名`,
	A.`二代团队编号`,
	DZ2.`二代会员姓名` AS 二代团队姓名,
	A.`团队编号`,
	L.`会员姓名` AS 团队姓名
FROM
(SELECT
	O.`会员编号`,
	O.`创建时间` AS 创建时间,
	SUM(O.`PV合计`) AS 报单PV,
	RecommedUP((SELECT U3.ID FROM ar_user U3 WHERE U3.USER_NAME = O.`会员编号`), 2) AS 店长编号,
	RecommedUP((SELECT U4.ID FROM ar_user U4 WHERE U4.USER_NAME = O.`会员编号`), 1) AS 团队编号,
	RecommedUP2((SELECT U5.ID FROM ar_user U5 WHERE U5.USER_NAME = O.`会员编号`)) AS 二代团队编号
FROM
	zr_order_vw O

		WHERE (O.`创建时间` >= @DATE_START AND O.`创建时间` < @DATE_STOP) AND O.`订单类型` = '报单'
		GROUP BY O.`会员编号`,O.`创建时间`) A
		LEFT JOIN ar_user U ON U.USER_NAME = A.`会员编号`
		LEFT JOIN ar_declaration_level D ON D.ID = U.DEC_LV
		LEFT JOIN ar_user_relation_new R ON R.USER_ID = U.ID
		LEFT JOIN ar_user U2 ON U2.ID = R.PARENT_UID
		LEFT JOIN `店长清单` DZ ON DZ.`店长会员编号` = A.`店长编号`
		LEFT JOIN `领导人清单` L ON L.`会员编号` = A.`团队编号`
		LEFT JOIN `二代领导人清单` DZ2 ON DZ2.`二代会员编号` = A.`二代团队编号`