如何在单个MySQL查询中计算来自两个表的行?
让我们首先创建一个表-
mysql> create table DemoTable1 ( Name varchar(40) );
使用插入命令在表中插入一些记录-
mysql> insert into DemoTable1 values('Chris'); mysql> insert into DemoTable1 values('Robert'); mysql> insert into DemoTable1 values('Mike');
使用select语句显示表中的所有记录-
mysql> select *from DemoTable1;
这将产生以下输出-
+--------+ | Name | +--------+ | Chris | | Robert | | Mike | +--------+ 3 rows in set (0.00 sec)
以下是创建第二个表的查询-
mysql> create table DemoTable2 ( Score int );
使用插入命令在表中插入一些记录-
mysql> insert into DemoTable2 values(86); mysql> insert into DemoTable2 values(98); mysql> insert into DemoTable2 values(75);
使用select语句显示表中的所有记录-
mysql> select *from DemoTable2;
这将产生以下输出-
+-------+ | Score | +-------+ | 86 | | 98 | | 75 | +-------+ 3 rows in set (0.00 sec)
以下是在一个查询中对来自两个表的行进行计数的查询-
mysql> select (select count(*) from DemoTable1) AS FirstTableCount, (select count(*) from DemoTable2) AS SecondTableCount from dual;
这将产生以下输出-
+-----------------+------------------+ | FirstTableCount | SecondTableCount | +-----------------+------------------+ | 3 | 3 | +-----------------+------------------+ 1 row in set (0.00 sec)