SQLsummary/活动/店长拉新统计.sql

-- 需要 临时表 做中间表
SELECT
	u3.`团队编号`,
	u4.REAL_NAME AS 团队姓名,
	u3.`会员编号`,
	u3.`会员姓名`,
	u3.LEVEL_NAME AS 会员级别,
	u3.PARTITION_DATE AS 加入日期
FROM
	(
	SELECT
		u.`会员编号`,
		u.`会员姓名`,
		us.PARTITION_DATE,
		dl.LEVEL_NAME,
		RecommedUP (( SELECT u2.ID FROM ar_user u2 WHERE u2.USER_NAME = u.`会员编号` ), 2 ) 团队编号 
	FROM
		临时会员 u 
		LEFT JOIN ar_user us ON us.USER_NAME = u.`会员编号`
		LEFT JOIN ar_declaration_level dl ON dl.ID = us.DEC_LV
	) u3
	LEFT JOIN ar_user u4 ON u4.USER_NAME = u3.`团队编号`



-- 直接查询 店长
SELECT
	u3.`店长编号`,
	u4.REAL_NAME AS 店长姓名,
	u3.USER_NAME AS 会员编号,
	u3.REAL_NAME AS 会员姓名,
	u3.LEVEL_NAME AS 会员级别,
	u3.PARTITION_DATE AS 加入日期 
FROM
	(
	SELECT
		us.USER_NAME,
		us.REAL_NAME,
		dl.LEVEL_NAME,
		us.PARTITION_DATE,
		RecommedUP (( SELECT u2.ID FROM ar_user u2 WHERE u2.USER_NAME = us.USER_NAME ), 2 ) 店长编号 
	FROM
		ar_user us
		LEFT JOIN ar_declaration_level dl ON us.DEC_LV = dl.ID 
	WHERE
		us.PARTITION_DATE >= '2021-03-09' 
	) u3
	LEFT JOIN ar_user u4 ON u4.USER_NAME = u3.`店长编号`


SELECT
	u3.`团队编号`,
	u4.REAL_NAME AS 团队姓名,
	u3.USER_NAME AS 会员编号,
	u3.REAL_NAME AS 会员姓名,
	u3.LEVEL_NAME AS 会员级别,
	u3.PARTITION_DATE AS 加入日期 
FROM
	(
	SELECT
		us.USER_NAME,
		us.REAL_NAME,
		dl.LEVEL_NAME,
		us.PARTITION_DATE,
		RecommedUP (( SELECT u2.ID FROM ar_user u2 WHERE u2.USER_NAME = us.USER_NAME ), 1 ) 团队编号 
	FROM
		ar_user us
		LEFT JOIN ar_declaration_level dl ON us.DEC_LV = dl.ID 
	WHERE
		us.PARTITION_DATE >= '2021-03-09' 
		AND dl.LEVEL_NAME = 'VIP会员' 
	) u3
	LEFT JOIN ar_user u4 ON u4.USER_NAME = u3.`团队编号`

-- 基础数据

SELECT
	u3.USER_NAME AS 会员编号,
	u3.REAL_NAME AS 会员姓名,
	u3.LEVEL_NAME AS 会员级别,
	u3.PARTITION_DATE AS 加入日期,
	o.PV AS 报单PV,
	u6.USER_NAME AS 推荐人编号,
	u6.REAL_NAME AS 推荐人姓名,
	d.`店号`,
	u3.`店长编号`,
	u4.REAL_NAME AS 店长姓名,
	u3.`团队编号`,
	u5.REAL_NAME AS 团队姓名
FROM
	(
	SELECT
		us.ID,
		us.USER_NAME,
		us.REAL_NAME,
		dl.LEVEL_NAME,
		us.PARTITION_DATE,
		RecommedUP (( SELECT u2.ID FROM ar_user u2 WHERE u2.USER_NAME = us.USER_NAME ), 2 ) 店长编号,
		RecommedUP(( SELECT u2.ID FROM ar_user u2 WHERE u2.USER_NAME = us.USER_NAME), 1) 团队编号
	FROM
		ar_user us
		LEFT JOIN ar_declaration_level dl ON us.DEC_LV = dl.ID
	WHERE
		us.PARTITION_DATE >= '2021-02-21' AND us.PARTITION_DATE <= '2021-04-01'
	) u3 
	LEFT JOIN ar_user u4 ON u4.USER_NAME = u3.`店长编号`
	LEFT JOIN ar_user u5 ON u5.USER_NAME = u3.`团队编号`
	LEFT JOIN `店长清单` d ON d.`店长会员编号` = u3.`店长编号`
	LEFT JOIN ar_order o ON o.USER_NAME = u3.USER_NAME
	INNER JOIN ar_user_relation_new n ON n.USER_ID = u3.ID
	INNER JOIN ar_user u6 ON u6.ID = n.PARENT_UID
	WHERE o.ORDER_TYPE = 'ZC'


-- 推荐人活动期间金额,PV

SELECT
	A.`推荐人编号`,
	A.`推荐人姓名`,
	IFNULL(SUM(V.`商品金额`),0) AS 金额,
	IFNULL(SUM(V.`PV合计`),0) AS PV
FROM
(SELECT
	u3.USER_NAME AS 会员编号,
	u3.REAL_NAME AS 会员姓名,
	u3.LEVEL_NAME AS 会员级别,
	u6.USER_NAME AS 推荐人编号,
	u6.REAL_NAME AS 推荐人姓名,
	o.PV AS 报单PV,
	u3.PARTITION_DATE AS 加入日期,
	d.`店号`,
	u3.`店长编号`,
	u4.REAL_NAME AS 店长姓名,
	u3.`团队编号`,
	u5.REAL_NAME AS 团队姓名
FROM
	(
	SELECT
		us.ID,
		us.USER_NAME,
		us.REAL_NAME,
		dl.LEVEL_NAME,
		us.PARTITION_DATE,
		RecommedUP (( SELECT u2.ID FROM ar_user u2 WHERE u2.USER_NAME = us.USER_NAME ), 2 ) 店长编号,
		RecommedUP(( SELECT u2.ID FROM ar_user u2 WHERE u2.USER_NAME = us.USER_NAME), 1) 团队编号
	FROM
		ar_user us
		LEFT JOIN ar_declaration_level dl ON us.DEC_LV = dl.ID
	WHERE
		us.PARTITION_DATE >= '2021-02-21' AND us.PARTITION_DATE <= '2021-04-01'
	) u3 
	LEFT JOIN ar_user u4 ON u4.USER_NAME = u3.`店长编号`
	LEFT JOIN ar_user u5 ON u5.USER_NAME = u3.`团队编号`
	LEFT JOIN `店长清单` d ON d.`店长会员编号` = u3.`店长编号`
	LEFT JOIN ar_order o ON o.USER_NAME = u3.USER_NAME
	LEFT JOIN ar_user_relation_new n ON n.USER_ID = u3.ID
	LEFT JOIN ar_user u6 ON u6.ID = n.PARENT_UID
	WHERE o.ORDER_TYPE = 'ZC' AND u3.LEVEL_NAME = 'VIP会员') A
	LEFT JOIN zr_order_vw V ON V.`会员编号` = A.`推荐人编号`
	AND V.`创建时间` >= '2021-02-21'
	AND V.`创建时间` <= '2021-04-01'
	AND V.`订单类型` <> '报单'
	GROUP BY A.`推荐人编号`

-- 所有会员业绩排名



SELECT
B.`会员编号`,
B.`会员姓名`,
B.`报单PV`,
B.`复消PV`,
D.`店号`,
D.`店长会员编号` AS 店长编号,
D.`店长姓名`,
L.`会员编号` AS 团队编号,
L.`会员姓名` AS 团队姓名
FROM
(SELECT
	A.`会员编号`,
	U.REAL_NAME AS 会员姓名,
	A.`报单PV`,
	A.`复消PV`,
	RecommedUP((SELECT U2.ID FROM ar_user U2 WHERE U2.USER_NAME = A.`会员编号`), 2) AS 店长编号,
	RecommedUP((SELECT U2.ID FROM ar_user U2 WHERE U2.USER_NAME = A.`会员编号`), 1) AS 团队编号
FROM 
(SELECT C.`会员编号`,SUM(C.`复消PV`) AS 复消PV,SUM(C.`报单PV`) AS 报单PV FROM (SELECT
	V2.`会员编号`,
	0 AS 复消PV,
	SUM(V2.`PV合计`) AS 报单PV
FROM
	zr_order_vw V2
WHERE V2.`创建时间` >= '2021-02-21' AND V2.`创建时间` <= '2021-04-01' AND V2.`订单类型` = '报单'
GROUP BY V2.`会员编号`
	UNION ALL
SELECT 
	V.`会员编号`,
	SUM(V.`PV合计`) AS 复消PV,
	0 AS 报单PV
FROM
	zr_order_vw V
WHERE V.`创建时间` >= '2021-02-21' AND V.`创建时间` <= '2021-04-01' AND V.`订单类型` <> '报单'
GROUP BY V.`会员编号`) C GROUP BY C.`会员编号` ) A
LEFT JOIN ar_user U ON U.USER_NAME = A.`会员编号` GROUP BY A.`会员编号`) B
LEFT JOIN `店长清单` D ON D.`店长会员编号` = B.`店长编号`
LEFT JOIN `领导人清单` L ON L.`会员编号` = B.`团队编号`
GROUP BY B.`会员编号`