要从不同的表中获取计数总和,请使用UNION ALL。让我们首先创建一个表-mysql> create table DemoTable1
-> (
-> Id int,
-> Name varchar(30)
-> );
使用插入命令在表中插入一些记录-mysql> insert into DemoTable1 values(10,'Chris Brown');
mysql> insert into DemoTable1 values(20,'David Miller');
mysql> insert into DemoTable1 values(30,'John Adam');
使用select语句显示表中的所有记录-mysql> select *from DemoTable1;
输出结果+------+--------------+
| Id | Name |
+------+--------------+
| 10 | Chris Brown |
| 20 | David Miller |
| 30 | John Adam |
+------+--------------+
3 rows in set (0.00 sec)
以下是创建第二个表的查询-mysql> create table DemoTable2
-> (
-> Amount int
-> );
使用插入命令在表中插入一些记录-mysql> insert into DemoTable2 values(100);
mysql> insert into DemoTable2 values(200);
mysql> insert into DemoTable2 values(300);
使用select语句显示表中的所有记录-mysql> select *from DemoTable2;
输出结果+--------+
| Amount |
+--------+
| 100 |
| 200 |
| 300 |
+--------+
3 rows in set (0.00 sec)
这是如何在单个查询中从不同表中获取计数总和-mysql> select sum(AllCount) AS Total_Count
-> from
-> (
-> (select count(*) AS AllCount from DemoTable1)
-> union all
-> (select count(*) AS AllCount from DemoTable2)
-> )t;
输出结果+-------------+
| Total_Count |
+-------------+
| 6 |
+-------------+
1 row in set (0.03 sec)