MySQL的：和（），并从多个表中加入 [英] MySQL: sum() and join from multiple tables

问题描述

简单的说，但是我找不到语法，甚至一个例子差得远！假设下表：

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

然后创建一个PHP while循环回显的结果。事情是这样的：

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

<?php

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

?>

我不明白这一点。任何帮助吗？我只是需要一个PHP的select语句，将在这里工作。

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

推荐答案

未经测试，但应该工作：

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

这篇关于MySQL的：和（），并从多个表中加入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！