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
	rs2 CURSOR FOR SELECT
		ud,
		zan,
		cai,
		ping,
		fen,
		shan,
		(
			zan * 1 - cai * 1 + ping * 1 + fen * 1 + shan * 1
		) paimin
	FROM
		lfsj_sort_user_info;

/* 声明游标 */
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;

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(
	'{"cai":"',
	bcai,
	'"cai":"',
	bcai,
	'","fen":',
	bfen,
	'","ping":',
	bping,
	'","shan":',
	bshan,
	'"}'
)
WHERE
	a.ID = ud_row;

INSERT INTO `kwc`.`cc` (`id`) VALUES ('aa' );


INSERT INTO `kwc`.`cc` (`id`) VALUES (concat(
	'{"cai":"',
	bcai,
	'"cai":"',
	bcai,
	'","fen":',
	bfen,
	'","ping":',
	bping,
	'","shan":',
	bshan,
	'"}'
) );

END
IF;

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

UNTIL Done
END
REPEAT
;

/* 关闭游标 */
CLOSE rs2;


END