统计排名

原创于 2016-06-29 18:35:36 发布 · 385 阅读

0 ·

CC 4.0 BY-SA版权

mysql 同时被 2 个专栏收录

21 篇文章

订阅专栏

mysql_postgresql

4 篇文章

订阅专栏

本文介绍了一个MySQL存储过程，该过程使用自定义公式计算用户的排名，并更新到两个不同的表中。通过游标和条件判断实现了高效的数据处理，适用于如论坛、社区等需要实时更新用户排名的场景。

CREATE DEFINER=`root`@`192.168.1.%` PROCEDURE `paimin`()
BEGIN
	DECLARE
		zan1 INT DEFAULT 1;

DECLARE
	cai1 INT DEFAULT 1;

DECLARE
	ping1 INT DEFAULT 1;

DECLARE
	fen1 INT DEFAULT 1;

DECLARE
	shan1 INT DEFAULT 1;

DECLARE
	bzan INT DEFAULT 1;

DECLARE
	bcai INT DEFAULT 1;

DECLARE
	bping INT DEFAULT 1;

DECLARE
	bfen INT DEFAULT 1;

DECLARE
	bshan INT DEFAULT 1;

DECLARE
	Done INT DEFAULT 0;

DECLARE
	CurrentPaiMing INT DEFAULT 0;

DECLARE
	ZUBOID VARCHAR (6);

DECLARE
	ZUBOGOLD INT DEFAULT 0;

DECLARE
	ud_row VARCHAR (6);

DECLARE
	paimin_row INT DEFAULT 0;

DECLARE
	paimin_index INT DEFAULT 1;

DECLARE
	rs3_ud_row VARCHAR (6);

DECLARE
	rs3_total_row INT DEFAULT 1;

DECLARE
	rs2 CURSOR FOR SELECT
		ud,
		zan,
		cai,
		ping,
		fen,
		shan,
		paimin
	FROM
		(
			SELECT
				ud,
				zan,
				cai,
				ping,
				fen,
				shan,
				(
					zan * zan1 - cai * 1 + ping * 1 + fen * 1 + shan * 1
				) paimin
			FROM
				lfsj_sort_user_info
		) pm
	ORDER BY
		paimin DESC;

-- DECLARE rs3 CURSOR for SELECT ud,total from lfsj_sort_user_info ORDER BY total,ud DESC;
DECLARE
	rs3 CURSOR FOR SELECT
		id AS ud,
		RANKING AS total
	FROM
		t_user_info
	ORDER BY
		RANKING desc;

/* 声明游标 */
DECLARE
	rs CURSOR FOR SELECT
		ZID,
		GOLD
	FROM
		(
			SELECT
				ZID,
				SUM(GOLD) GOLD
			FROM
				T_ANCHOR_GAINS
			GROUP BY
				ZID
		) A
	ORDER BY
		GOLD DESC;

/* 异常处理 */
DECLARE
	CONTINUE HANDLER FOR SQLSTATE '02000'
SET Done = 1;

/* 打开游标 */
OPEN rs;

/* 逐个取出当前记录LingQi字段的值，需要进行最大值的判断 */
FETCH NEXT
FROM
	rs INTO ZUBOID,
	ZUBOGOLD;

/* 遍历数据表 */
REPEAT

IF NOT Done THEN

SET CurrentPaiMing = CurrentPaiMing + 1;

UPDATE t_user_info
SET RANKING = CurrentPaiMing
WHERE
	ID = ZUBOID;

UPDATE lfsj_sort_user_info
SET shan = ZUBOGOLD
WHERE
	ud = ZUBOID;


END
IF;

FETCH NEXT
FROM
	rs INTO ZUBOID,
	ZUBOGOLD;

UNTIL Done
END
REPEAT
;

/* 关闭游标 */
CLOSE rs;

/* 声明游标 */
/* 异常处理 */
-- DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET Done2 = 1;
UPDATE t_user_info
SET RANKING = 0;

COMMIT;

SET Done = 0;

/* 打开游标 */
OPEN rs2;

/* 逐个取出当前记录LingQi字段的值，需要进行最大值的判断 */
FETCH NEXT
FROM
	rs2 INTO ud_row,
	bzan,
	bcai,
	bping,
	bfen,
	bshan,
	paimin_row;

/* 遍历数据表 */
REPEAT

IF NOT Done THEN
	UPDATE t_user_info a
SET a.RANKING = paimin_row,
 a.SNUM = concat(
	'{"zan":',
	bzan,
	',"cai":',
	bcai,
	',"ping":',
	bping,
	',"fen":',
	bfen,
	',"shan":',
	bshan,
	'}'
)
WHERE
	a.ID = ud_row;

UPDATE lfsj_sort_user_info
SET total = paimin_row
WHERE
	ud = ud_row;


END
IF;

FETCH NEXT
FROM
	rs2 INTO ud_row,
	bzan,
	bcai,
	bping,
	bfen,
	bshan,
	paimin_row;

UNTIL Done
END
REPEAT
;

/* 关闭游标 */
CLOSE rs2;

COMMIT;
SET Done = 0;

OPEN rs3;

FETCH next
FROM
	rs3 INTO rs3_ud_row,
	rs3_total_row;


REPEAT

IF NOT Done THEN
	UPDATE t_user_info a
SET a.RANKING = paimin_index
WHERE
	id = rs3_ud_row;


SET paimin_index = paimin_index + 1;


END
IF;

FETCH next
FROM
	rs3 INTO rs3_ud_row,
	rs3_total_row;

UNTIL Done
END
REPEAT
;

CLOSE rs3;


END