mysql 游标 排行_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