php - 多个查询的结果求和

Question

我对 PHP/MySQL 很陌生，我需要对分组的多个查询的结果进行求和。

例如，我有一个这样的表:

Toy   |  Material 1  | Qt Mat 1 | Material 2 | Qt Mat 2 | Material 3 | Qt Mat 3

House |     Wood     |    2 kg  |    Cloth   |   1 kg   |   Rubber   |   0.5 kg
Horse |     Wood     |    3 kg  |   Rubber   |   2 kg   |            |
Plane |    Plastic   |    1 kg  |   Steel    |   2 kg   |   Rubber   |   0.2 kg 
Doll  |     Cloth    |    2 kg  |            |          |            |
Car   |   Rubber     |    1 kg  |    Plastic |   2 kg   |            |

假设我想 build 一座房子、一个玩偶和一辆汽车。
我的购物 list 是:

Wood - 2 kg (House)
Cloth - 3 kg (1 for House, 2 for Doll)
Rubber - 1.5 kg (0.5 for House, 1 for Car)
Plastic - 2 kg (Car) 

如何使用 PHP 和 MySQL 生成它？

Answer 1

SELECT Material,SUM(Qty) as NetQty
FROM (
      SELECT  Material1 as Material, QtMat1 as Qty
      FROM tableName
      WHERE Toy IN ('House','Doll','Car')

      UNION ALL 

      SELECT  Material2, QtMat2
      FROM tableName
      WHERE Toy IN ('House','Doll','Car')

      UNION ALL 

      SELECT  Material3, QtMat3
      FROM tableName
      WHERE Toy IN ('House','Doll','Car')

    ) As qry

   GROUP BY qry.Material