SQLsummary/报表/各期奖金总表.sql

SET @PERIOD_MIN = 158;
SET @PERIOD_MAX = 159;

SELECT
A.`期数`,
A2.`总金额`,
A2.`总PV`,
A3.`服务奖`,
A3.`推广奖`,
A3.`业绩奖`,
A3.`消费奖`,
A3.`共享奖`,
A3.`管理奖`,
A3.`团队奖`,
A.`报单PV` * A3.`VIP奖参考比例` AS `VIP奖`,
A2.`总PV` * A3.`荣衔奖参考比例` AS `荣衔奖`,
SUM(A3.`服务奖`+
A3.`推广奖`+
A3.`业绩奖`+
A3.`消费奖`+
A3.`共享奖`+
A3.`管理奖`+
A3.`团队奖`+
A.`报单PV` * A3.`VIP奖参考比例`+
A2.`总PV` * A3.`荣衔奖参考比例`) AS 总奖金,
SUM(A3.`服务奖`+
A3.`推广奖`+
A3.`业绩奖`+
A3.`消费奖`+
A3.`共享奖`+
A3.`管理奖`+
A3.`团队奖`+
A.`报单PV` * A3.`VIP奖参考比例`+
A2.`总PV` * A3.`荣衔奖参考比例`)/A2.`总金额` AS 总金额占比,
SUM(A3.`服务奖`+
A3.`推广奖`+
A3.`业绩奖`+
A3.`消费奖`+
A3.`共享奖`+
A3.`管理奖`+
A3.`团队奖`+
A.`报单PV` * A3.`VIP奖参考比例`+
A2.`总PV` * A3.`荣衔奖参考比例`)/A2.`总PV` AS 总PV占比
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 A.`期数` = A2.`期数`
	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