SQLsummary/报表/奖金排名.sql

SET @PERIOD_MIN = 155;
SET @PERIOD_MAX = 157;

SELECT
	B1.`会员编号`,
	B1.`会员姓名`,
	E.LEVEL_NAME AS 荣衔等级,
	B1.`总奖金`
FROM
(SELECT
	A1.`会员编号`,
	A1.`会员姓名`,
	SUM(A1.`总奖金`) AS 总奖金
FROM
(SELECT
	B.PERIOD_NUM AS 期数,
	U.USER_NAME AS 会员编号,
	U.REAL_NAME AS 会员姓名,
	B.BONUS_TOTAL AS 总奖金
FROM
	ar_calc_bonus B
	LEFT JOIN ar_user U ON U.ID = B.USER_ID
	WHERE B.PERIOD_NUM >= @PERIOD_MIN AND B.PERIOD_NUM <= @PERIOD_MAX AND B.BONUS_TOTAL >= 1
	UNION ALL
SELECT
	J.`期数`,
	J.`会员编号`,
	J.`会员姓名`,
	J.`总奖金`
FROM
	`奖金数据表` J
	WHERE J.`期数` >= @PERIOD_MIN AND J.`期数` <= @PERIOD_MAX AND J.`总奖金` >= 1)A1
	GROUP BY A1.`会员编号`,A1.`会员姓名`
	ORDER BY SUM(A1.`总奖金`) DESC
	LIMIT 0,15)B1
	LEFT JOIN ar_user U ON U.USER_NAME = B1.`会员编号`
	LEFT JOIN ar_employ_level E ON E.ID = U.EMP_LV