在mysql中使用多个表的列的多个求和函数给出的值不正确

Question

我有几个MySQL表，如下所示

Table1
Att1 | Flag
xxx  |  1


Table2
Att1 | Flag
xxx  |  2
yyy  |  2


Table3
Att1 | Flag
xxx  |  3
yyy  |  3


Table4
Att1 | Flag
xxx  |  4

Table5
Att1 | Flag
xxx  |  6

每个表都有一些属性和上面的属性。我想求和每个表的标志属性并查看它们。下面是MySQL代码。

create view block as 
select sum(table1.flag)as table1, sum(table2.flag)as table2,
sum(table3.flag) as table3,sum(table4.flag) as table4,
sum(table5.flag) as table5 
from table1,table2,table3,table4,table5;

我在我的视野中看到的是：

table1|table2|table3|table4|table5
4     |8     |12    |16    |24

其实我想在我的视图中看到的是：

table1|table2|table3|table4|table5
1     |4     |6     |4     |6

帮帮我！提前道谢！

Answer 1

每个表中的每一行都与另一个表中的每一行连接，然后对结果进行求和。这不是你想要的。试试看：

create view block as 
select 'table1' as tableName, sum(flag) from 
table1
UNION ALL
select 'table2' as tableName, sum(flag) from 
table2
UNION ALL
select 'table3' as tableName, sum(flag) from 
table3
UNION ALL
select 'table4' as tableName, sum(flag) from 
table4
UNION ALL
select 'table5' as tableName, sum(flag) from 
table5

Answer 2

请尝试以下操作：

select table1, table2, table3, table4, table5
from (select sum(table1.flag)as table1) t1 cross join
     (select sum(table2.flag)as table2) t2 cross join
     (select sum(table3.flag)as table2) t3 cross join
     (select sum(table4.flag)as table2) t4 cross join
     (select sum(table5.flag)as table2) t5

您的查询正在执行交叉联接，这是表之间的笛卡尔积。避免这种情况的最佳方法是为每个表执行单独的聚合。您也可以在select子句中执行以下操作：

select 
     (select sum(table1.flag)as table1) as t1,
     (select sum(table2.flag)as table2) as t2,
     (select sum(table3.flag)as table2) as t3,
     (select sum(table4.flag)as table2) as t4,
     (select sum(table5.flag)as table2) as t5