SQLsummary/报表/业绩单检查.sql

SELECT
	B.`会员编号`,
	B.`期数`,
	A.`报单金额`,
	A.`报单PV`,
	B.`商品金额`,
	B.`PV合计`,
	(A.`报单PV`-B.`PV合计`) AS 核对结果
FROM
	(SELECT
		W.`会员编号`,
		W.`期数`,
		SUM(W.`商品金额`) AS 商品金额,
		SUM(W.`PV合计`) AS PV合计
	FROM zr_order_vw W 
	WHERE 期数 = 161
		GROUP BY W.`会员编号`) B
	LEFT JOIN
	(SELECT
		Y.`会员编号`,
		Y.`结算期数` AS 期数,
		SUM(Y.`报单金额`) AS 报单金额,
		SUM(Y.`报单PV`) AS 报单PV
	FROM `业绩单` Y
	WHERE 结算期数 = 161
		GROUP BY Y.`会员编号`) A
		ON	A.`会员编号` = B.`会员编号`
		
		
		SELECT P.PERIOD_NUM AS 期数,
			A.新增银钻,
			(A.新增银钻/A.新增合计) AS 银钻比例,
			A.新增金钻,
			(A.新增金钻/A.新增合计) AS 金钻比例,
			A.新增VIP,
			(A.新增VIP/A.新增合计) AS VIP比例,
			A.新增合计,
			A.复消会员
		FROM ar_period P,((SELECT COUNT(`实时会员级别`) AS 新增银钻 FROM zr_user_vw V1 WHERE 实时会员级别 = "银钻会员" AND V1.`加入期数` = P.PERIOD_NUM) 新增银钻,
			(SELECT COUNT(`实时会员级别`) AS 新增金钻 FROM zr_user_vw V2 WHERE 实时会员级别 = "金钻会员" AND V2.`加入期数` = P.PERIOD_NUM) 新增金钻,
			(SELECT COUNT(`实时会员级别`) AS 新增VIP FROM zr_user_vw V3 WHERE 实时会员级别 = "VIP会员" AND V3.`加入期数` = P.PERIOD_NUM) 新增VIP,
			(SELECT COUNT(`实时会员级别`) AS 新增合计 FROM zr_user_vw V4 WHERE V4.`加入期数` = P.PERIOD_NUM) 新增合计,
			(SELECT COUNT(DISTINCT 会员编号) AS 复消会员 FROM zr_product_pkg_vw WHERE 期数 = P.PERIOD_NUM AND (订单类型 = "复消" OR 订单类型 = "积分")) 复消会员) A
		WHERE P.PERIOD_NUM = 166