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