PHP+MySQL下8个同结构表对应值求和至新表（限最小行数）

前提准备 #

1. 提前创建第9个表table9，结构与其他8个表一致：

CREATE TABLE table9 LIKE table1;

2. 确保所有表有可用于确定行顺序的字段（比如主键id、创建时间等），保证对应位置的行能够正确匹配。

步骤1：获取8个表中的最小行数 #

先通过SQL查询出所有表中最少的行数，后续只对前N行（N为最小行数）进行求和：

SELECT MIN(row_count) AS min_rows
FROM (
    SELECT COUNT(*) AS row_count FROM table1
    UNION ALL SELECT COUNT(*) FROM table2
    UNION ALL SELECT COUNT(*) FROM table3
    UNION ALL SELECT COUNT(*) FROM table4
    UNION ALL SELECT COUNT(*) FROM table5
    UNION ALL SELECT COUNT(*) FROM table6
    UNION ALL SELECT COUNT(*) FROM table7
    UNION ALL SELECT COUNT(*) FROM table8
) AS counts;

步骤2：关联多表并求和插入新表 #

利用窗口函数ROW_NUMBER()给每个表的行添加序号，然后按序号关联所有表，对对应字段求和后插入table9。

静态字段SQL（适合字段较少的情况）

如果你的表字段固定且不多，可以直接写死字段：

INSERT INTO table9 (col1, col2, col3)
SELECT
    IFNULL(t1.col1,0)+IFNULL(t2.col1,0)+IFNULL(t3.col1,0)+IFNULL(t4.col1,0)+IFNULL(t5.col1,0)+IFNULL(t6.col1,0)+IFNULL(t7.col1,0)+IFNULL(t8.col1,0) AS col1,
    IFNULL(t1.col2,0)+IFNULL(t2.col2,0)+IFNULL(t3.col2,0)+IFNULL(t4.col2,0)+IFNULL(t5.col2,0)+IFNULL(t6.col2,0)+IFNULL(t7.col2,0)+IFNULL(t8.col2,0) AS col2,
    IFNULL(t1.col3,0)+IFNULL(t2.col3,0)+IFNULL(t3.col3,0)+IFNULL(t4.col3,0)+IFNULL(t5.col3,0)+IFNULL(t6.col3,0)+IFNULL(t7.col3,0)+IFNULL(t8.col3,0) AS col3
FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS row_num FROM table1
) t1
JOIN (SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS row_num FROM table2) t2 ON t1.row_num = t2.row_num
JOIN (SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS row_num FROM table3) t3 ON t1.row_num = t3.row_num
JOIN (SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS row_num FROM table4) t4 ON t1.row_num = t4.row_num
JOIN (SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS row_num FROM table5) t5 ON t1.row_num = t5.row_num
JOIN (SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS row_num FROM table6) t6 ON t1.row_num = t6.row_num
JOIN (SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS row_num FROM table7) t7 ON t1.row_num = t7.row_num
JOIN (SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS row_num FROM table8) t8 ON t1.row_num = t8.row_num
WHERE t1.row_num <= (
    SELECT MIN(row_count) FROM (
        SELECT COUNT(*) AS row_count FROM table1 UNION ALL SELECT COUNT(*) FROM table2
        UNION ALL SELECT COUNT(*) FROM table3 UNION ALL SELECT COUNT(*) FROM table4
        UNION ALL SELECT COUNT(*) FROM table5 UNION ALL SELECT COUNT(*) FROM table6
        UNION ALL SELECT COUNT(*) FROM table7 UNION ALL SELECT COUNT(*) FROM table8
    ) AS counts
);

注：用IFNULL()处理NULL值，避免NULL导致求和结果为NULL；ORDER BY id替换为你的实际排序字段。

PHP动态生成SQL（适合字段较多的情况）

如果表字段较多，用PHP动态获取字段并生成SQL，避免重复劳动：

<?php
// 数据库连接配置
$host = 'localhost';
$dbname = 'your_database';
$username = 'your_username';
$password = 'your_password';

try {
    // 初始化PDO连接
    $pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8mb4", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // 获取最小行数
    $minRowsResult = $pdo->query("
        SELECT MIN(row_count) AS min_rows FROM (
            SELECT COUNT(*) AS row_count FROM table1 UNION ALL SELECT COUNT(*) FROM table2
            UNION ALL SELECT COUNT(*) FROM table3 UNION ALL SELECT COUNT(*) FROM table4
            UNION ALL SELECT COUNT(*) FROM table5 UNION ALL SELECT COUNT(*) FROM table6
            UNION ALL SELECT COUNT(*) FROM table7 UNION ALL SELECT COUNT(*) FROM table8
        ) AS counts
    ");
    $minRows = $minRowsResult->fetch(PDO::FETCH_ASSOC)['min_rows'];

    if ($minRows <= 0) {
        echo "无数据可求和";
        exit;
    }

    // 获取表中所有数值型字段（从table1读取，结构一致）
    $fieldsStmt = $pdo->query("DESCRIBE table1");
    $numericFields = [];
    while ($fieldInfo = $fieldsStmt->fetch(PDO::FETCH_ASSOC)) {
        // 筛选数值类型字段，可根据实际情况调整类型列表
        $numericTypes = ['int', 'bigint', 'float', 'double', 'decimal'];
        if (in_array(strtolower(explode('(', $fieldInfo['Type'])[0]), $numericTypes)) {
            $numericFields[] = $fieldInfo['Field'];
        }
    }

    // 生成求和字段片段
    $sumClauses = [];
    foreach ($numericFields as $field) {
        $tableFieldExprs = [];
        foreach (range(1, 8) as $tableIdx) {
            $tableFieldExprs[] = "IFNULL(t{$tableIdx}.{$field}, 0)";
        }
        $sumClauses[] = implode(' + ', $tableFieldExprs) . " AS {$field}";
    }
    $sumSql = implode(', ', $sumClauses);

    // 生成表子查询与连接条件
    $tableSubqueries = [];
    foreach (range(1, 8) as $tableIdx) {
        $tableSubqueries[] = "(SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS row_num FROM table{$tableIdx}) t{$tableIdx}";
    }
    $joinSql = implode(' JOIN ', $tableSubqueries);
    $joinConditions = [];
    for ($i = 2; $i <= 8; $i++) {
        $joinConditions[] = "t1.row_num = t{$i}.row_num";
    }
    $joinSql .= ' ON ' . implode(' AND ', $joinConditions);

    // 最终插入SQL
    $insertSql = "
        INSERT INTO table9 (" . implode(', ', $numericFields) . ")
        SELECT {$sumSql}
        FROM {$joinSql}
        WHERE t1.row_num <= {$minRows}
    ";

    // 执行插入
    $pdo->exec($insertSql);
    echo "求和完成，成功插入 {$minRows} 条数据到table9";

} catch (PDOException $e) {
    die("执行失败: " . $e->getMessage());
}

// 关闭连接
$pdo = null;
?>

注意事项 #

• 行顺序一致性：必须保证所有表的行排序规则一致（比如都按主键升序），否则对应位置的行求和会出错。
• 性能优化：如果表数据量较大，给排序字段（比如id）添加索引，提升ROW_NUMBER()的执行效率。
• 空值处理：必须用IFNULL()或COALESCE()处理可能的NULL值，否则求和结果会变为NULL。

内容的提问来源于stack exchange，提问作者DHQ DoITC JAIPUR