我有一张叫查拉的桌子:
+----------+------------+----------------+-------------+------------+----------+----------+--------------+--------------+-----------+-----------+
| chara_id | chara_name | chara_class_id | chara_level | chara_gold | chara_hp | chara_mp | chara_max_hp | chara_max_mp | chara_atk | chara_def |
+----------+------------+----------------+-------------+------------+----------+----------+--------------+--------------+-----------+-----------+
| 1 | LawrenceX | 1 | 1 | 0 | 11610 | 1000 | 0 | 0 | 7 | 3 |
| 2 | Testo | 3 | 1 | 0 | 11465 | 900 | 0 | 0 | 9 | 1 |
| 3 | Viscocent | 2 | 1 | 0 | 11570 | 1100 | 0 | 0 | 5 | 5 |
| 4 | Piatos | 1 | 1 | 0 | 12470 | 1000 | 0 | 0 | 7 | 3 |
| 5 | Hello | 1 | 1 | 0 | 12600 | 1000 | 0 | 0 | 2 | 8 |
| 6 | Sample | 3 | 1 | 0 | 12700 | 900 | 0 | 0 | 9 | 1 |
| 7 | tester | 2 | 1 | 0 | 12500 | 1100 | 0 | 0 | 5 | 5 |
| 8 | Sampuro | 0 | 1 | 0 | 11700 | 100 | 0 | 0 | 5 | 5 |
+----------+------------+----------------+-------------+------------+----------+----------+--------------+--------------+-----------+-----------+
我有一张chara_base_stat表:
+--------------+----------------+------------------+---------------+---------------+----------------+----------------+
| base_stat_id | chara_class_id | chara_base_level | chara_base_hp | chara_base_mp | chara_base_atk | chara_base_def |
+--------------+----------------+------------------+---------------+---------------+----------------+----------------+
| 1 | 1 | 1 | 1000 | 1000 | 5 | 5 |
| 2 | 1 | 2 | 1100 | 1100 | 10 | 10 |
| 3 | 1 | 3 | 1200 | 1200 | 15 | 15 |
| 4 | 1 | 4 | 1300 | 1300 | 20 | 20 |
| 5 | 1 | 5 | 1400 | 1400 | 25 | 25 |
| 6 | 2 | 1 | 900 | 1100 | 7 | 3 |
| 7 | 2 | 2 | 1000 | 1200 | 14 | 6 |
| 8 | 2 | 3 | 1100 | 1300 | 21 | 9 |
| 9 | 2 | 4 | 1200 | 1400 | 28 | 12 |
| 10 | 2 | 5 | 1300 | 1500 | 35 | 19 |
| 11 | 3 | 1 | 1100 | 900 | 2 | 8 |
| 12 | 3 | 2 | 1200 | 1000 | 4 | 16 |
| 13 | 3 | 3 | 1300 | 1100 | 6 | 24 |
| 14 | 3 | 4 | 1400 | 1200 | 8 | 32 |
| 15 | 3 | 5 | 1500 | 1300 | 16 | 40 |
+--------------+----------------+------------------+---------------+---------------+----------------+----------------+
我有一个脚本,它每分钟更新一次hp的值(在这里使用cronjob):
$sql = "UPDATE chara SET chara_hp = chara_hp +100";
$stmt = $db->prepare($sql);
$stmt->execute();
我的奋斗:
如果chara表中的当前字符数不超过chara-base-hp,则如何将chara表中的当前字符数加上+100
基本上,我只想在当前血量小于当前血量的情况下,从血量表中恢复那些玩家的血量
注意:当前的chara_base_hp对应于玩家的当前等级和玩家的chara_class_id。
到目前为止,我在这里有一个疑问:
SELECT ch.chara_hp, bs.chara_base_hp FROM chara ch inner join chara_base_stat bs on(ch.chara_class_id = bs.chara_class_id and ch.chara_level = bs.chara_base_level)
又回来了
+----------+---------------+
| chara_hp | chara_base_hp |
+----------+---------------+
| 13210 | 1000 |
| 14070 | 1000 |
| 14200 | 1000 |
| 13170 | 900 |
| 14100 | 900 |
| 13065 | 1100 |
| 14300 | 1100 |
+----------+---------------+
我想在sql中执行此操作:
if(chara_hp < chara_base_hp){ update chara set chara_hp = chara_hp + 100 }
最佳答案
UPDATE
chara ch INNER JOIN chara_base_stat bs
ON ch.chara_class_id = bs.chara_class_id
AND ch.chara_level = bs.chara_base_level
SET
ch.chara_hp = LEAST(bs.chara_base_hp, ch.chara_hp + 100)
WHERE
ch.chara_hp < bs.chara_base_hp
关于php - 在MySQL中使用IF语句进行SELECT UPDATE,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/16272524/