mysql - 从动态多表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  |
+-------+---------+-------+

我的问题是当条件是动态表名称(从表进程获取)时如何从多个表获取总数量。表名可以从一个查询中选择其他表来获取总数量字段吗？此示例查询:

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

我找到了解决方案..我在 1 个 View 中创建 View tb_hd、tb_rl、tb_fu、tb_tr 联合