mysql多表计数,MySQL选择计数，多个表

I have three 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;

Here is my query:

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

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.

Any help is greatly appreciated :)

解决方案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

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

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

A union query results in

1, 100 # duplicate collapsed

2, 200