脚本定时更新排行榜数据

表

+————————-+

| team_base_data  这个表是基础数据          |

| team_bat_info      这个表是控制当前访问的表和总数     |

| team_rank_0          这个是排序以后的表 跟下面的一样 两个 做交叉使用   |

| team_rank_1             |

+————————-+

CREATE TABLE `team_base_data` (
  `iuin` bigint(20) NOT NULL,
  `iarea` int(11) NOT NULL,
  `teamscore` int(11) DEFAULT NULL,
  PRIMARY KEY (`iuin`,`iarea`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `team_bat_info` (
  `total` int(11) DEFAULT NULL,
  `act_table` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `team_rank_0` (
  `iuin` bigint(20) NOT NULL,
  `iarea` int(11) NOT NULL,
  `teamscore` int(11) DEFAULT NULL,
  `rank` int(11) DEFAULT NULL,
  PRIMARY KEY (`iuin`,`iarea`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

插入team_bat_info 一条基础数据

insert into team_bat_info values(0,0);

脚本如下：

#!/bin/bash
cmd="SELECT act_table FROM team_bat_info LIMIT 1"
cnt=$(mysql -uroot -pxxxxxxx -P3306 test -s -e "${cmd}")
next_table="team_rank_$[(${cnt}+1)%2]"
cmd2="
TRUNCATE TABLE ${next_table};
INSERT INTO ${next_table} SELECT iuin,iarea,teamscore,(@rowno:=@rowno+1) AS rank FROM team_base_data,(select (@rowno:=0)) tmp_tb ORDER BY teamscore DESC;
UPDATE team_bat_info SET total=(SELECT MAX(rank) FROM ${next_table});
UPDATE team_bat_info SET act_table=act_table+1;
"
cnt2=$(mysql -uroot -pxxxxxxx -P3306 test -s -e "${cmd2}")
exit

结果如下

mysql> select * from team_rank_1;
+------+-------+-----------+------+
| iuin | iarea | teamscore | rank |
+------+-------+-----------+------+
|    1 |     1 |         1 |    1 |
|    2 |     1 |         1 |    2 |
|    2 |     2 |         1 |    3 |
|    2 |     3 |         1 |    4 |
|    2 |     4 |         1 |    5 |
|    3 |     1 |         1 |    6 |
|    3 |     3 |         1 |    7 |
|    3 |     4 |         1 |    8 |
+------+-------+-----------+------+
8 rows in set (0.00 sec)

mysql> select * from team_bat_info;
+-------+-----------+
| total | act_table |
+-------+-----------+
|     8 |         1 |
+-------+-----------+
1 row in set (0.00 sec)