我有几个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
帮帮我！提前道谢！
请尝试以下操作：
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
每个表中的每一行都与另一个表中的每一行连接，然后对结果进行求和。这不是你想要的。试试看：
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
