从动态多个表mysql获取总和数量

Question

我有这样的表主：

item
+-------+---------+
|item_id|item_name|
+-------+---------+
|  001  |  Car A  |
|  002  |  Car B  |
+-------+---------+
process
+-------+---------+----------+
|proc_id|proc_name|proc_table|
+-------+---------+----------+
|   1   |    HD   |   tb_HD  |
|   2   |    RL   |   tb_RL  |
|   3   |    FU   |   tb_FU  |
|   4   |    TR   |   tb_TR  |
+-------+---------+----------+
item_process
+------------+-------+
|proc_item_id|proc_id|
+------------+-------+
|    001     |   1   |
|    001     |   2   |
|    001     |   4   |
|    002     |   2   |
|    002     |   3   |
|    002     |   4   |
+------------+-------+
tb_HD
+----+---+
|item|qty|
+----+---+
|001 |100|
+----+---+
tb_RL
+----+---+
|item|qty|
+----+---+
|001 |50 |
|002 |70 |
+----+---+
tb_FU
+----+---+
|item|qty|
+----+---+
|002 | 20|
|002 |150|
+----+---+
tb_TR
+----+---+
|item|qty|
+----+---+
|002 |30 |
+----+---+

当我选择item_id 002

时，我想要这样的结果

+-------+---------+-------+
|item_id|proc_name|sum_qty|
+-------+---------+-------+
|  002  |    RL   |   70  |
|  002  |    FU   |  170  |
|  002  |    TR   |   30  |
+-------+---------+-------+

我的问题是，当条件是动态表名称(从表过程中获取(时，我如何从多个表获得总和。表名可以从一个查询中的其他表中的其他表中获取sum QTY字段吗？此示例查询：

SELECT a.item_id, 
   b.proc_name, 
   b.proc_table, 
   (SELECT SUM(c.qty) FROM b.proc_table c GROUP BY c.item ) AS qty 
FROM item_process a
LEFT JOIN process b ON a.proc_id=b.proc_id;

sqlfiddle sqlfiddle

Answer 1

我找到了解决方案。我创建查看tb_hd，tb_rl，tb_fu，tb_tr union as in 1 View