SQLsummary/报表/奖金排名（期）.sql

SET @PERIOD = 161;

SELECT
	A1.`会员编号`,
	A1.`会员姓名`,
	A1.`荣衔等级`,
	A1.`总奖金1`,
	B1.`总奖金2`,
	C1.`总奖金3`,
	D1.`总奖金4`,
	E1.`总奖金5`,
	F1.`总奖金6`,
	G1.`总奖金7`,
	H1.`总奖金8`,
	I1.`总奖金9`,
	J1.`总奖金10`,
	K1.`总奖金11`,
	L1.`总奖金12`
FROM
(SELECT
	U1.USER_NAME AS 会员编号,
	U1.REAL_NAME AS 会员姓名,
	E1.LEVEL_NAME AS 荣衔等级,
	B.BONUS_TOTAL AS 总奖金1
FROM
	ar_calc_bonus B
	LEFT JOIN ar_user U1 ON U1.ID = B.USER_ID
	LEFT JOIN ar_employ_level E1 ON E1.ID = U1.EMP_LV
	WHERE B.PERIOD_NUM = @PERIOD
	ORDER BY B.BONUS_TOTAL DESC LIMIT 20
	) A1
	
	LEFT JOIN 
	(SELECT
	U1.USER_NAME AS 会员编号,
	B.BONUS_TOTAL AS 总奖金2
FROM
	ar_calc_bonus B
	LEFT JOIN ar_user U1 ON U1.ID = B.USER_ID
	WHERE B.PERIOD_NUM = @PERIOD-11) B1
	 ON A1.`会员编号` = B1.`会员编号`
	 LEFT JOIN 
	(SELECT
	U1.USER_NAME AS 会员编号,
	B.BONUS_TOTAL AS 总奖金3
FROM
	ar_calc_bonus B
	LEFT JOIN ar_user U1 ON U1.ID = B.USER_ID
	WHERE B.PERIOD_NUM = @PERIOD-10)C1 ON C1.`会员编号` = B1.`会员编号`
	
	LEFT JOIN 
	(SELECT
	U1.USER_NAME AS 会员编号,
	B.BONUS_TOTAL AS 总奖金4
FROM
	ar_calc_bonus B
	LEFT JOIN ar_user U1 ON U1.ID = B.USER_ID
	WHERE B.PERIOD_NUM = @PERIOD-9)D1 ON D1.`会员编号` = C1.`会员编号`
	
	LEFT JOIN 
	(SELECT
	U1.USER_NAME AS 会员编号,
	B.BONUS_TOTAL AS 总奖金5
FROM
	ar_calc_bonus B
	LEFT JOIN ar_user U1 ON U1.ID = B.USER_ID
	WHERE B.PERIOD_NUM = @PERIOD-8)E1 ON D1.`会员编号` = E1.`会员编号`
	
	LEFT JOIN 
	(SELECT
	U1.USER_NAME AS 会员编号,
	B.BONUS_TOTAL AS 总奖金6
FROM
	ar_calc_bonus B
	LEFT JOIN ar_user U1 ON U1.ID = B.USER_ID
	WHERE B.PERIOD_NUM = @PERIOD-7)F1 ON E1.`会员编号` = F1.`会员编号`
	
	LEFT JOIN 
	(SELECT
	U1.USER_NAME AS 会员编号,
	B.BONUS_TOTAL AS 总奖金7
FROM
	ar_calc_bonus B
	LEFT JOIN ar_user U1 ON U1.ID = B.USER_ID
	WHERE B.PERIOD_NUM = @PERIOD-6)G1 ON F1.`会员编号` = G1.`会员编号`
	
	LEFT JOIN 
	(SELECT
	U1.USER_NAME AS 会员编号,
	B.BONUS_TOTAL AS 总奖金8
FROM
	ar_calc_bonus B
	LEFT JOIN ar_user U1 ON U1.ID = B.USER_ID
	WHERE B.PERIOD_NUM = @PERIOD-5)H1 ON H1.`会员编号` = G1.`会员编号`
	
	LEFT JOIN 
	(SELECT
	U1.USER_NAME AS 会员编号,
	B.BONUS_TOTAL AS 总奖金9
FROM
	ar_calc_bonus B
	LEFT JOIN ar_user U1 ON U1.ID = B.USER_ID
	WHERE B.PERIOD_NUM = @PERIOD-4)I1 ON I1.`会员编号` = H1.`会员编号`
	
	LEFT JOIN 
	(SELECT
	U1.USER_NAME AS 会员编号,
	B.BONUS_TOTAL AS 总奖金10
FROM
	ar_calc_bonus B
	LEFT JOIN ar_user U1 ON U1.ID = B.USER_ID
	WHERE B.PERIOD_NUM = @PERIOD-3)J1 ON J1.`会员编号` = I1.`会员编号`
	
	LEFT JOIN 
	(SELECT
	U1.USER_NAME AS 会员编号,
	B.BONUS_TOTAL AS 总奖金11
FROM
	ar_calc_bonus B
	LEFT JOIN ar_user U1 ON U1.ID = B.USER_ID
	WHERE B.PERIOD_NUM = @PERIOD-2)K1 ON K1.`会员编号` = J1.`会员编号`
	
	LEFT JOIN 
	(SELECT
	U1.USER_NAME AS 会员编号,
	B.BONUS_TOTAL AS 总奖金12
FROM
	ar_calc_bonus B
	LEFT JOIN ar_user U1 ON U1.ID = B.USER_ID
	WHERE B.PERIOD_NUM = @PERIOD-1)L1 ON L1.`会员编号` = K1.`会员编号`