SQLsummary/活动/线下优佳生活馆工作室招商.sql

SET @PERIOD = 188;

SELECT
*
FROM


(SELECT
	T.`会员编号`,
	T.`会员姓名`,
	T.`创建时间`,
	T.`期数`,
	T.`存货编码`,
	T.`存货名称`,
	T.`推荐人编号`,
	T.`推荐人姓名`,
	L.LEVEL_NAME AS `推荐人级别`,
	T.`店长编号`,
	D.`店长姓名`,
	'3000'奖励,
	T.`商品金额`,
	T.`PV合计`
FROM
(SELECT
	V.`会员编号`,
	U.`会员姓名`,
	A.`创建时间`,
	A.`期数`,
	A.`存货编码`,
	A.`存货名称`,
	U.`推荐人编号`,
	U.`推荐人姓名`,
	U.`级别`,
	U.`店长编号`,
	SUM(V.`商品金额`) AS 商品金额,
	SUM(V.`PV合计`) AS PV合计
FROM
	(SELECT * FROM zr_order_vw WHERE `订单类型` = '报单') V
RIGHT JOIN
	(SELECT
		W.`会员编号`,
		W.`存货编码`,
		W.`存货名称`,
		W.`订单类型`,
		W.`创建时间`,
		W.`期数`
	FROM
		(SELECT * FROM zr_order_vw WHERE `订单类型` = '报单') W
	WHERE W.`订单类型` = '报单' AND (W.`存货编码` = 'TA172' OR W.`存货编码` = 'WP31V-1' OR W.`存货编码` = 'TA225') GROUP BY W.`会员编号` ) A ON A.`会员编号` = V.`会员编号`
RIGHT JOIN 
	(SELECT
		U.USER_NAME AS 会员编号,
		U.REAL_NAME AS 会员姓名,
		U.PERIOD_AT AS 加入期数,
		U2.USER_NAME AS 推荐人编号,
		U2.REAL_NAME AS 推荐人姓名,
		U2.DEC_LV AS 级别,
		RecommedUP((SELECT U3.ID FROM ar_user U3 WHERE U3.USER_NAME = U2.USER_NAME ), 2) AS 店长编号
	FROM
		ar_user_relation_new m 
		LEFT JOIN ar_user U ON U.ID = m.USER_ID
		LEFT JOIN ar_user U2 ON U2.ID = m.PARENT_UID
	WHERE U.PERIOD_AT >= 140) U ON V.`会员编号` = U.`会员编号`	
GROUP BY V.`会员编号`) T
LEFT JOIN `店长清单` D ON D.`店长会员编号` = T.`店长编号`
LEFT JOIN ar_declaration_level L ON T.`级别` = L.ID
WHERE T.`商品金额` >= 36000 ) S

WHERE S.`期数` = @PERIOD

/*SET @PERIOD = 140;

SELECT
	T.`会员编号`,
	T.`会员姓名`,
	T.`创建时间`,
	T.`期数`,
	T.`存货编码`,
	T.`存货名称`,
	T.`推荐人编号`,
	T.`推荐人姓名`,
	L.LEVEL_NAME AS `推荐人级别`,
	T.`店长编号`,
	D.`店长姓名`,
	'3000'奖励,
	T.`商品金额`,
	T.`PV合计`
FROM
(SELECT
	V.`会员编号`,
	U.`会员姓名`,
	A.`创建时间`,
	A.`期数`,
	A.`存货编码`,
	A.`存货名称`,
	U.`推荐人编号`,
	U.`推荐人姓名`,
	U.`级别`,
	U.`店长编号`,
	SUM(V.`商品金额`) AS 商品金额,
	SUM(V.`PV合计`) AS PV合计
FROM
	(SELECT * FROM zr_order_vw WHERE `订单类型` = '报单') V
RIGHT JOIN
	(SELECT
		W.`会员编号`,
		W.`存货编码`,
		W.`存货名称`,
		W.`订单类型`,
		W.`创建时间`,
		W.`期数`
	FROM
		(SELECT * FROM zr_order_vw WHERE `订单类型` = '报单') W
	WHERE W.`订单类型` = '报单' AND (W.`存货编码` = 'TA172' OR W.`存货编码` = 'WP31V-1' OR W.`存货编码` = 'TA225') AND W.`期数` >= @PERIOD GROUP BY W.`会员编号` ) A ON A.`会员编号` = V.`会员编号`
RIGHT JOIN 
	(SELECT
		U.USER_NAME AS 会员编号,
		U.REAL_NAME AS 会员姓名,
		U.PERIOD_AT AS 加入期数,
		U2.USER_NAME AS 推荐人编号,
		U2.REAL_NAME AS 推荐人姓名,
		U2.DEC_LV AS 级别,
		RecommedUP((SELECT U3.ID FROM ar_user U3 WHERE U3.USER_NAME = U2.USER_NAME ), 2) AS 店长编号
	FROM
		ar_user_relation_new m 
		LEFT JOIN ar_user U ON U.ID = m.USER_ID
		LEFT JOIN ar_user U2 ON U2.ID = m.PARENT_UID
	WHERE U.PERIOD_AT >= @PERIOD) U ON V.`会员编号` = U.`会员编号`	
WHERE V.`期数` >= @PERIOD
GROUP BY V.`会员编号`) T
LEFT JOIN `店长清单` D ON D.`店长会员编号` = T.`店长编号`
LEFT JOIN ar_declaration_level L ON T.`级别` = L.ID
WHERE T.`商品金额` >= 36000*/