php - 从 2 个表中选择项目的总值并更新另一个表的值(复杂查询)

Question

我的情况是，我想计算要在我制作的战斗模块上使用的玩家攻击力，但只是想知道我实际上有两个选择:

计算服务器造成的伤害。(我当前的选项)

1. 使用 PHP 计算 DAMAGE DEALT 和 UPDATE 服务器数据库值。
2. 传递 2 个字符 ID，然后计算查询中的所有内容并更新所有内容(这可能吗)。

问题:我可以在查询中执行此操作吗？(选项 B)

我当前的设置:
1 个角色有 4 个项目，我通过在客户端添加所有 4 个项目 atk 和角色基本 atk 来计算角色 atk。 (我认为这很容易出现安全漏洞)然后更新服务器端的值。

这是我的表格:

角色:

+----------+------------+----------------+-------------+------------+----------+----------+----------+-----------+-----------+
| chara_id | chara_name | chara_class_id | chara_level | chara_gold | chara_hp | chara_mp | chara_xp | chara_atk | chara_def |
+----------+------------+----------------+-------------+------------+----------+----------+----------+-----------+-----------+
|        1 | LawrenceX  |              1 |           5 |        230 |     -175 |     1000 |        0 |         7 |         3 |
|        3 | Viscocent  |              2 |           2 |         96 |     -206 |     1100 |     1700 |         5 |         5 |
|        4 | Piatos     |              1 |           1 |        120 |      -60 |     1000 |        0 |         7 |         3 |
|        5 | Hello      |              1 |           1 |        300 |      -50 |     1000 |      200 |         2 |         8 |
|        6 | Sample     |              3 |           2 |        251 |      -85 |      900 |        0 |         9 |         1 |
|        8 | Sampuro    |              2 |           1 |        170 |      895 |     1100 |      700 |         5 |         5 |
|       12 | fail       |              2 |           3 |        481 |     1100 |     1300 |        0 |        21 |         9 |
|       13 | new        |              1 |           1 |       1000 |      -80 |     1000 |        0 |         5 |         5 |
+----------+------------+----------------+-------------+------------+----------+----------+----------+-----------+-----------+

项目:

+---------+-----------------+-----------+----------+----------+----------+---------------------------------+-------------------------------------------------+------------+
|       0 | None            |         0 |        0 |        0 |        0 | pics/none.png                   |                                                 |        400 |
|       1 | Axe             |         1 |      220 |       10 |        0 | pics/weapons/axe.png            | Another lumberjack axe is another man's weapon. |        200 |
|       2 | Wooden Sword    |         1 |       70 |        0 |        0 | pics/weapons/wooden-sword.png   | A wooden sword, 99% made from wood              |        225 |
|       3 | Dagger          |         1 |       60 |        5 |        0 | pics/weapons/dagger.png         | A Dagger, Cheap and Sharp                       |         55 |
|       4 | Bow             |         1 |      120 |        1 |        0 | pics/weapons/bow.png            | The basics and simplest of all bows.            |        120 |
|       5 | Helmet          |         4 |        0 |       50 |        0 | pics/headgears/helmet.png       | iron helmet - made from an iron pot scraps.     |        155 |
|       6 | Tunic           |         2 |       10 |       10 |        0 | pics/armors/tunic.png           | A peasants tunic.                               |         50 |
|       7 | Armour          |         2 |        0 |       75 |        0 | pics/armors/armour.png          |                                                 |        150 |
|       8 | Necklace        |         3 |       25 |       15 |        0 | pics/accessories/necklace.png   |                                                 |        199 |
|       9 | Studded Leather |         2 |       25 |       60 |        0 | pics/armors/studded-leather.png |                                                 |        240 |
+---------+-----------------+-----------+----------+----------+----------+---------------------------------+-------------------------------------------------+------------+

设备:

+----------+----------+-----------+-------------+----------+---------+
| equip_id | chara_id | weapon_id | headgear_id | armor_id | ring_id |
+----------+----------+-----------+-------------+----------+---------+
|        3 |        1 |        14 |           5 |        6 |       8 |
|        5 |        3 |         4 |           5 |        6 |       8 |
|        6 |        4 |        11 |           5 |        7 |       8 |
|        7 |        5 |        12 |           5 |        6 |       8 |
|        8 |        6 |         3 |          16 |        7 |       8 |
|       10 |        8 |        15 |           5 |        7 |       8 |
|       13 |       12 |        14 |           5 |        6 |      17 |
|       40 |       13 |         3 |           5 |        7 |       8 |
+----------+----------+-----------+-------------+----------+---------+

表关系:

1 chara = 1 equipment  
1 weapon_id, armor_id, ring_id, headgear_id = 1 item (total of 4 items, headgear_id = 1 item).

我可以使用此查询获取角色的装备(KUDOS @JC):

SELECT i1.item_atk weapon_atk,i1.item_def weapon_def,
       i2.item_atk headgear_atk,
       i2.item_def headgear_def,
       i3.item_atk armor_atk,
       i3.item_def armor_def,
       i4.item_atk ring_atk,
       i4.item_def ring_def
       FROM equipment e LEFT JOIN
       item i1 ON e.weapon_id = i1.item_id LEFT JOIN
       item i2 ON e.headgear_id = i2.item_id LEFT JOIN
       item i3 ON e.armor_id = i3.item_id LEFT JOIN
       item i4 ON e.ring_id = i4.item_id 
       WHERE e.chara_id = 1

结果:

+------------+------------+--------------+--------------+-----------+-----------+----------+----------+
| weapon_atk | weapon_def | headgear_atk | headgear_def | armor_atk | armor_def | ring_atk | ring_def |
+------------+------------+--------------+--------------+-----------+-----------+----------+----------+
|        275 |         25 |            0 |           50 |        10 |        10 |       25 |       15 |
+------------+------------+--------------+--------------+-----------+-----------+----------+----------+

现在我想合计该角色装备的 atk 和 def 并在该查询中返回它

预期结果:

+------------+------------+
| total_atk  | total_def  |
+------------+------------+
|        310 |        100 |
+------------+------------+

Answer 1

这是我能想到的最简单的方法。

SELECT IFNULL(W.item_atk, 0) + IFNULL(H.item_atk, 0) + IFNULL(A.item_atk, 0) + IFNULL(R.item_atk, 0) AS total_atk
    , IFNULL(W.item_def, 0) + IFNULL(H.item_def, 0) + IFNULL(A.item_def, 0) + IFNULL(R.item_def, 0) AS total_def
    FROM equipment E
    LEFT JOIN item W ON W.item_id = E.weapon_id
    LEFT JOIN item H ON H.item_id = E.headgear_id
    LEFT JOIN item A ON A.item_id = E.armor_id
    LEFT JOIN item R ON R.item_id = E.ring_id
    WHERE E.chara_id = 1

我已重命名表的别名以轻松跟踪它们。我使用了 IFNULL 以防角色没有特定的设备。

================================================== =====================================

伙计，我刚刚又做了一个查询，我认为这比上面的查询要快。不过，我还没有测试过它们。

SELECT SUM(IFNULL(I.item_atk, 0)) AS total_atk
    , SUM(IFNULL(I.item_def, 0)) AS total_def
    FROM equipment E
    LEFT JOIN item I ON I.item_id = E.weapon_id
        OR I.item_id = E.headgear_id
        OR I.item_id = E.armor_id
        OR I.item_id = E.ring_id
    WHERE E.chara_id = 1
    GROUP BY E.chara_id