php - mySQL 选择计数，多个表

Question

我有三个表:

table1, table2, table3

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

$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`

当我运行这个查询时，我得到:

count   price
19      5609399
8       946000
4       0

这在 PHP 中循环时不起作用，因为它们是 3 个独立的值，我只返回“count = 19 and price = 5609399”。它们并没有全部以 count 或 price 的形式加在一起。

非常感谢任何帮助:)

Answer 1

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 对值

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 删除了重复项，所以如果你的计数/总和是准确的

1, 100
1, 100
2, 200

联合查询结果为

1, 100    # duplicate collapsed
2, 200