MySQL选择计数，多个表 [英] mySQL select count, multiple tables

问题描述

我有三个表:

table1, table2, table3

我正在尝试从每个表中获取行的总数以及价格列的总和，因此例如:

I am trying to get a total count of rows from each table as well as sum of price column, so for example:

$r['count'] = total rows of all 3 tables combined;
$r['price'] = sum of all prices added together in all 3 tables combined;

这是我的查询:

SELECT COUNT(*) AS `count`, SUM(price) AS `price` FROM `table1` UNION
SELECT COUNT(*) AS `count`, SUM(price) AS `price` FROM `table2` UNION
SELECT COUNT(*) AS `count`, SUM(price) AS `price` FROM `table3`

运行此查询时，我得到:

When I run this query, I am getting:

count   price
19      5609399
8       946000
4       0

当在PHP中循环时，这又不起作用，因为它们是3个单独的值，而我仅返回"count = 19 and price = 5609399".它们并没有全部以count或price的形式合计.

Which in turn does not work when looped out in PHP as they are 3 separate values and I am only being returned "count = 19 and price = 5609399". They don't all come out totaled together as count or price.

非常感谢您的帮助:)

推荐答案

SELECT COUNT(*) AS `count`, SUM(price) AS `price`
FROM
(
SELECT price from `table1` UNION ALL
SELECT price FROM `table2` UNION ALL
SELECT price FROM `table3`
) X

或通常将3对值组合在一起

or generally to combine 3 pairs of values

select sum(`count`) `count`, sum(`price`) `price`
FROM
(
SELECT COUNT(*) AS `count`, SUM(price) AS `price` FROM `table1` UNION ALL
SELECT COUNT(*) AS `count`, SUM(price) AS `price` FROM `table2` UNION ALL
SELECT COUNT(*) AS `count`, SUM(price) AS `price` FROM `table3`
) X

通常，在合并表格时使用UNION ALL，而不是UNION，因为UNION删除了重复项，因此，如果您的计数/总和完全相同

Generally, use UNION ALL when combining tables, not UNION because UNION removed duplicates, so if your count/sum were exactly

1, 100
1, 100
2, 200

联合查询结果

1, 100    # duplicate collapsed
2, 200

这篇关于MySQL选择计数，多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！