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

SET @MONTH1 = 202101;
SET @YEAR1 = 2021;

SELECT
	M1.`会员编号`,
	M1.`会员姓名`,
	M1.`荣衔等级`,
	A1.`总奖金1`,
	B1.`总奖金2`,
	C1.`总奖金3`,
	D1.`总奖金4`,
	E1.`总奖金5`,
	F1.`总奖金6`,
	G1.`总奖金7`,
	H1.`总奖金8`,
	I1.`总奖金9`,
	J1.`总奖金10`,
	K1.`总奖金11`,
	L1.`总奖金12`,
	M1.`合计`
FROM
(SELECT
	U1.USER_NAME AS 会员编号,
	U1.REAL_NAME AS 会员姓名,
	E1.LEVEL_NAME AS 荣衔等级,
	SUM(B.BONUS_TOTAL) AS 合计
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.CALC_YEAR = 2021
	GROUP BY B.USER_ID ORDER BY 合计 DESC LIMIT 20) M1
LEFT JOIN
(SELECT
	U1.USER_NAME AS 会员编号,
	SUM(B.BONUS_TOTAL) AS 总奖金1
FROM
	ar_calc_bonus B
	LEFT JOIN ar_user U1 ON U1.ID = B.USER_ID
	WHERE B.CALC_MONTH = @MONTH1
	GROUP BY B.USER_ID)A1
	ON A1.`会员编号` = M1.`会员编号`
	LEFT JOIN 
	(SELECT
	U1.USER_NAME AS `会员编号`,
	SUM(B.BONUS_TOTAL) AS 总奖金2
FROM
	ar_calc_bonus B
	LEFT JOIN ar_user U1 ON U1.ID = B.USER_ID
	WHERE B.CALC_MONTH = @MONTH1+1
	GROUP BY B.USER_ID)B1
	ON B1.会员编号 = A1.`会员编号`
	
	LEFT JOIN 
	(SELECT
	U1.USER_NAME AS `会员编号`,
	SUM(B.BONUS_TOTAL) AS 总奖金3
FROM
	ar_calc_bonus B
	LEFT JOIN ar_user U1 ON U1.ID = B.USER_ID
	WHERE B.CALC_MONTH = @MONTH1+2
	GROUP BY B.USER_ID)C1
	ON B1.会员编号 = C1.`会员编号`
	
	LEFT JOIN 
	(SELECT
	U1.USER_NAME AS `会员编号`,
	SUM(B.BONUS_TOTAL) AS 总奖金4
FROM
	ar_calc_bonus B
	LEFT JOIN ar_user U1 ON U1.ID = B.USER_ID
	WHERE B.CALC_MONTH = @MONTH1+3
	GROUP BY B.USER_ID)D1
	ON D1.会员编号 = C1.`会员编号`
	
	LEFT JOIN 
	(SELECT
	U1.USER_NAME AS `会员编号`,
	SUM(B.BONUS_TOTAL) AS 总奖金5
FROM
	ar_calc_bonus B
	LEFT JOIN ar_user U1 ON U1.ID = B.USER_ID
	WHERE B.CALC_MONTH = @MONTH1+4
	GROUP BY B.USER_ID)E1
	ON E1.会员编号 = D1.`会员编号`
	
	LEFT JOIN 
	(SELECT
	U1.USER_NAME AS `会员编号`,
	SUM(B.BONUS_TOTAL) AS 总奖金6
FROM
	ar_calc_bonus B
	LEFT JOIN ar_user U1 ON U1.ID = B.USER_ID
	WHERE B.CALC_MONTH = @MONTH1+5
	GROUP BY B.USER_ID)F1
	ON F1.会员编号 = E1.`会员编号`
	
	LEFT JOIN 
	(SELECT
	U1.USER_NAME AS `会员编号`,
	SUM(B.BONUS_TOTAL) AS 总奖金7
FROM
	ar_calc_bonus B
	LEFT JOIN ar_user U1 ON U1.ID = B.USER_ID
	WHERE B.CALC_MONTH = @MONTH1+6
	GROUP BY B.USER_ID)G1
	ON G1.会员编号 = F1.`会员编号`
	
	LEFT JOIN 
	(SELECT
	U1.USER_NAME AS `会员编号`,
	SUM(B.BONUS_TOTAL) AS 总奖金8
FROM
	ar_calc_bonus B
	LEFT JOIN ar_user U1 ON U1.ID = B.USER_ID
	WHERE B.CALC_MONTH = @MONTH1+7
	GROUP BY B.USER_ID)H1
	ON H1.会员编号 = G1.`会员编号`
	
	LEFT JOIN 
	(SELECT
	U1.USER_NAME AS `会员编号`,
	SUM(B.BONUS_TOTAL) AS 总奖金9
FROM
	ar_calc_bonus B
	LEFT JOIN ar_user U1 ON U1.ID = B.USER_ID
	WHERE B.CALC_MONTH = @MONTH1+8
	GROUP BY B.USER_ID)I1
	ON H1.会员编号 = I1.`会员编号`
	
	LEFT JOIN 
	(SELECT
	U1.USER_NAME AS `会员编号`,
	SUM(B.BONUS_TOTAL) AS 总奖金10
FROM
	ar_calc_bonus B
	LEFT JOIN ar_user U1 ON U1.ID = B.USER_ID
	WHERE B.CALC_MONTH = @MONTH1+9
	GROUP BY B.USER_ID)J1
	ON J1.会员编号 = H1.`会员编号`
	
	LEFT JOIN 
	(SELECT
	U1.USER_NAME AS `会员编号`,
	SUM(B.BONUS_TOTAL) AS 总奖金11
FROM
	ar_calc_bonus B
	LEFT JOIN ar_user U1 ON U1.ID = B.USER_ID
	WHERE B.CALC_MONTH = @MONTH1+10
	GROUP BY B.USER_ID)K1
	ON K1.会员编号 = J1.`会员编号`
	
	LEFT JOIN 
	(SELECT
	U1.USER_NAME AS `会员编号`,
	SUM(B.BONUS_TOTAL) AS 总奖金12
FROM
	ar_calc_bonus B
	LEFT JOIN ar_user U1 ON U1.ID = B.USER_ID
	WHERE B.CALC_MONTH = @MONTH1+11
	GROUP BY B.USER_ID)L1
	ON L1.会员编号 = K1.`会员编号`
	ORDER BY M1.`合计` DESC