MySQL获取AVR并保存在新字段中

Question

Hello I have this problem and can't find a solution: I have 4 tables with same structure for example as follows:

1. Table 1: Result
2. Table 2: Store 1
3. Table 3: Store 2

4. Table 4: Store 3

   • Tables fields: ID - Code - Name - Value

I need a query to read the "Value" for each specific record from tables (Store 1 - Store 2 - Store 3) and calculate the average and save it in table (Result)... and go on for the next record until it's done.

Note: I'm using PHP and MySQL...

Thanks in advanced...

SELECT
    result.id,
    result.`code`,
    result.`name`,
    result.value,
    term1.value,
    term2.value,
    term3.value
FROM result
INNER JOIN store1 ON result.`code` = store1.`code`
INNER JOIN store2 ON result.`code` = store2.`code`
INNER JOIN store3 ON result.`code` = store3.`code`
WHERE result.`code` = 123456
ORDER BY result.serial ASC

Answer 1

The average is just the sum of the values divided by the number of values (3), this is grade school arithmetic.

UPDATE result AS r
JOIN store1 AS s1 ON s1.code = r.code
JOIN store2 AS s2 ON s2.code = r.code
JOIN store3 AS s3 ON s3.code = r.code
SET r.value = (s1.value+s2.value+s3.value)/3

To do lots of columns, you can generate the SQL in PHP:

$cols = array('col1', 'col2', 'col3', ...);
$sets = implode(', ', array_map(function($col) {
    return "r.$col = (s1.$col + s2.$col + s3.$col)/3";
}, $cols));
$sql = "UPDATE result AS r
        JOIN store1 AS s1 ON s1.code = r.code
        JOIN store2 AS s2 ON s2.code = r.code
        JOIN store3 AS s3 ON s3.code = r.code
        SET $sets";

If you're using PHP before 5.3.0, you can define a named function to call it with array_map

function make_assignment($col) {
    return "r.$col = (s1.$col + s2.$col + s3.$col)/3";
}
$sets = implode(', ', array_map('make_assignment', $cols));