我有三张桌子:
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
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 对值
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
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)