我有一张叫查拉的桌子:

    +----------+------------+----------------+-------------+------------+----------+----------+--------------+--------------+-----------+-----------+
    | 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/

10-13 00:55