MySQL：sum（）并从多个表连接

Simple to say, but I can't find the syntax or even an example even close! Assume the following tables:

简单的说,但我找不到语法甚至一个例子甚至接近!假设以下表格:

Table:'Red'
Fields: id | sm | md | lg
Data:    1 | 3  | 5  | 7
         2 | 9  | 8  | 7
         3 | 2  | 4  | 6

Table:'White'
Fields: id | sm | md | lg
Data:    1 | 0  | 0  | 0
         2 | 0  | 0  | 0
         3 | 0  | 0  | 0

Table:'Blue'
Fields: id | sm | md | lg
Data:    1 | 1  | 1  | 1
         2 | 1  | 1  | 1
         3 | 1  | 1  | 1

All i want is to total everything up, but keep the rows like the following table:

我想要的只是总结一切,但保持行如下表:

Table:'Total'
Fields: id | sm | md | lg
Data:    1 |  4 | 6  | 8
         2 | 10 | 9  | 8
         3 |  3 | 5  | 7

Then create a while loop in PHP to echo back the results. Something like this:

然后在PHP中创建一个while循环以回显结果。像这样的东西:

<?php

while($row = mysql_fetch_array($get_totals))
{
echo <td>".$row[sm]."</td><td>".$row[md]."</td><td>".$row[lg]."</td>";
}

?>

I can't figure this out. Any help? I just need a php select statement that will work here.

我无法弄清楚这一点。有帮助吗?我只需要一个可以在这里工作的php select语句。

1 个解决方案

#1

Not tested but should work:

没有测试但应该工作:

SELECT id, SUM(sm) as sm, SUM(md) as md, SUM(lg) as lg FROM (
   SELECT * FROM Red
   UNION ALL
   SELECT * FROM White
   UNION ALL
   SELECT * FROM Blue
) AS somealias 
GROUP BY id