CREATE TABLE `lv` (
`lv` INT NOT NULL AUTO_INCREMENT PRIMARY KEY
COMMENT 'レベル',
`exp` INT NOT NULL COMMENT '必要経験値'
) ENGINE = MYISAM COMMENT = 'レベルテーブル';
INSERT INTO `lv` (`exp`)
VALUES ('100'),('200'),('350'),('600'),('900'),('1350');
lv |
exp |
1 |
100 |
2 |
200 |
3 |
350 |
4 |
600 |
5 |
900 |
6 |
1350 |
経験値の範囲を表示
SET @start_exp:=0;
SELECT lv,@start_exp,@start_exp:=exp exp FROM `lv`
lv |
start_exp |
end_exp |
1 |
0 |
100 |
2 |
100 |
200 |
3 |
200 |
350 |
4 |
350 |
600 |
5 |
600 |
900 |
6 |
900 |
1350 |
経験値のパーセンテージを表示
開始経験値と終了経験値の間に挟むのがポイント
SET @user_exp:=670;
SET @start_exp:=0;
SELECT lv,@start_exp start_exp,
IF(exp<=@user_exp,100,
IF(@start_exp>@user_exp,0,
ceil((@user_exp-@start_exp)/(exp-@start_exp)*100))) percent,
@start_exp:=exp end_exp FROM `lv`
lv |
start_exp |
percent |
end_exp |
1 |
0 |
100 |
100 |
2 |
100 |
100 |
200 |
3 |
200 |
100 |
350 |
4 |
350 |
100 |
600 |
5 |
600 |
24 |
900 |
6 |
900 |
0 |
1350 |