MySQL中从动态关联多表获取数量总和的技术问询

没问题，我来帮你搞定这个MySQL多表关联统计的需求！先咱们先明确下你的表结构关系，方便后续理解：

现有表结构 #

item表（主物品表）

process表（流程定义表，每个流程对应独立的数据表）

item_process表（物品与流程的关联关系表）

根据你的需求，要从这些动态关联的表中统计数量总和，我提供两种方案，你可以根据实际场景选择：

方案一：固定流程表场景（流程表数量少且不会新增） #

如果你的process表中的流程数量不多，且后续不会频繁新增流程表，直接用UNION ALL合并各流程表的统计数据，再关联主表即可：

SELECT 
    i.item_id,
    i.item_name,
    SUM(t.record_count) AS total_count
FROM item i
JOIN item_process ip ON i.item_id = ip.proc_item_id
JOIN process p ON ip.proc_id = p.proc_id
JOIN (
    -- 这里假设每个流程表都有item_id字段关联主物品表，统计记录数；如果是数值字段总和，把COUNT(*)换成SUM(your_field)
    SELECT 'tb_HD' AS proc_table, item_id, COUNT(*) AS record_count FROM tb_HD GROUP BY item_id
    UNION ALL
    SELECT 'tb_RL' AS proc_table, item_id, COUNT(*) AS record_count FROM tb_RL GROUP BY item_id
    UNION ALL
    SELECT 'tb_FU' AS proc_table, item_id, COUNT(*) AS record_count FROM tb_FU GROUP BY item_id
    UNION ALL
    SELECT 'tb_TR' AS proc_table, item_id, COUNT(*) AS record_count FROM tb_TR GROUP BY item_id
) t ON p.proc_table = t.proc_table AND i.item_id = t.item_id
GROUP BY i.item_id, i.item_name;

方案二：动态流程表场景（流程表会动态新增） #

如果后续会不断新增流程和对应的表，手动维护UNION ALL太麻烦，可以用动态SQL自动生成统计语句：

SET @sql = NULL;

-- 自动生成每个流程表的统计语句，拼接成UNION ALL结构
SELECT GROUP_CONCAT(DISTINCT
    CONCAT(
        'SELECT ''', proc_table, ''' AS proc_table, item_id, COUNT(*) AS record_count FROM ', proc_table, ' GROUP BY item_id'
    ) SEPARATOR ' UNION ALL '
) INTO @sql
FROM process;

-- 拼接完整的统计查询语句
SET @sql = CONCAT('
SELECT 
    i.item_id,
    i.item_name,
    SUM(t.record_count) AS total_count
FROM item i
JOIN item_process ip ON i.item_id = ip.proc_item_id
JOIN process p ON ip.proc_id = p.proc_id
JOIN (', @sql, ') t ON p.proc_table = t.proc_table AND i.item_id = t.item_id
GROUP BY i.item_id, i.item_name
');

-- 执行动态SQL
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

注意事项 #

1. 所有流程表（如tb_HD、tb_RL）必须包含item_id字段，用于和item表关联；
2. 如果你的“数量总和”是指某个数值字段（比如quantity）的总和，把语句中的COUNT(*)替换成SUM(quantity)即可；
3. 动态SQL需要确保process表中的proc_table字段存储的是合法的表名，且你有足够的数据库权限执行动态语句；
4. 如果某些物品在关联的流程表中没有数据，会被过滤掉；如果需要保留所有物品（包括无数据的），把JOIN换成LEFT JOIN，并把SUM(t.record_count)改成SUM(COALESCE(t.record_count, 0))。

内容的提问来源于stack exchange，提问作者NeoS