SQLsummary/报表/总金额占比.sql

SET @PERIOD_MIN = 158;
SET @PERIOD_MAX = 160;

SELECT
A.`期数`,
A2.`总金额`,
SUM(A3.`服务奖`+
A3.`推广奖`+
A3.`业绩奖`+
A3.`消费奖`+
A3.`共享奖`+
A3.`管理奖`+
A3.`团队奖`+
A.`报单PV` * A3.`VIP奖参考比例`+
A2.`总PV` * A3.`荣衔奖参考比例`) AS 总奖金,
A3.`服务奖`/A2.`总金额` AS 服务奖占比,
A3.`推广奖`/A2.`总金额` AS 推广奖占比,
A3.`业绩奖`/A2.`总金额` AS 业绩奖占比,
A3.`消费奖`/A2.`总金额` AS 消费奖占比,
A3.`共享奖`/A2.`总金额` AS 共享奖占比,
A3.`管理奖`/A2.`总金额` AS 管理奖占比,
A3.`团队奖`/A2.`总金额` AS 团队奖占比,
A.`报单PV` * A3.`VIP奖参考比例`/A2.`总金额` AS `VIP奖占比`,
A2.`总PV` * A3.`荣衔奖参考比例`/A2.`总金额` AS `荣衔奖占比`,
SUM(A3.`服务奖`+
A3.`推广奖`+
A3.`业绩奖`+
A3.`消费奖`+
A3.`共享奖`+
A3.`管理奖`+
A3.`团队奖`+
A.`报单PV` * A3.`VIP奖参考比例`+
A2.`总PV` * A3.`荣衔奖参考比例`)/A2.`总金额` AS 总金额占比
FROM
(SELECT
		O.`期数`,
		SUM( O.`PV合计` ) AS 报单PV 
	FROM
		zr_order_vw O 
	WHERE
		O.`期数` >= @PERIOD_MIN AND O.`期数` <= @PERIOD_MAX
		AND O.`订单类型` = '报单' GROUP BY O.`期数`) A
	 LEFT JOIN 
 (SELECT
	O2.`期数`,
	SUM( O2.`商品金额` ) AS 总金额,
	SUM( O2.`PV合计` ) AS 总PV
	FROM 
		zr_order_vw O2 
	WHERE	O2.`期数` >= @PERIOD_MIN AND O2.`期数` <= @PERIOD_MAX GROUP BY O2.`期数`) A2 ON A2.`期数` = A.`期数`
	LEFT JOIN
(SELECT
	B.PERIOD_NUM AS 期数,
	SUM(B.ORI_BONUS_BD) AS 服务奖,
	SUM(B.ORI_BONUS_TG) AS 推广奖,
	SUM(B.ORI_BONUS_YJ) AS 业绩奖,
	SUM(B.ORI_BONUS_XF) AS 消费奖,
	SUM(B.ORI_BONUS_GX) AS 共享奖,
	SUM(B.ORI_BONUS_GL) AS 管理奖,
	SUM(B.ORI_BONUS_QY) AS 团队奖,
	C.key_value AS VIP奖参考比例,
	C2.key_value AS 荣衔奖参考比例
FROM
	ar_calc_bonus B
	LEFT JOIN 参数表 C ON C.key_name = 'VIP奖参考比例'
	LEFT JOIN 参数表 C2 ON C2.key_name = '荣衔奖参考比例'
	WHERE B.PERIOD_NUM >= @PERIOD_MIN AND B.PERIOD_NUM <= @PERIOD_MAX GROUP BY B.PERIOD_NUM) A3 ON A3.`期数` = A2.`期数`
	GROUP BY A.`期数`
	ORDER BY A.`期数` ASC