MySQL count(*) 来自多个表?
mysqlmysqli database更新于 2024/1/8 23:09:00
要对多个表实现此操作，请使用 UNION ALL。
语法如下
select sum(variableName.aliasName) from ( select count(*) as yourAliasName from yourTableName1 UNION ALL select count(*) as yourAliasName from yourTableName2 ) yourVariableName;
让我们实现上述语法。在这里，我使用的是包含更多表的示例数据库。
我们使用的两个表是
- userdemo
- wheredemo
以下是显示两个表的所有记录的查询。查询如下，显示来自表"userdemo"的记录。
mysql> select *from userdemo;
以下是输出 −
+--------+----------+------------------+ | UserId | UserName | RegisteredCourse | +--------+----------+------------------+ | 1 | John | Java | | 2 | Larry | C | | 3 | Carol | C++ | | 4 | Mike | C# | +--------+----------+------------------+ 4 rows in set (0.08 sec)
查询如下，显示来自表‘wheredemo’的记录。
mysql> select *from wheredemo;
以下是输出 −
+------+---------+ | Id | Name | +------+---------+ | 101 | Maxwell | | 110 | David | | 1000 | Carol | | 1100 | Bob | | 115 | Sam | +------+---------+ 5 rows in set (0.20 sec)
以下是从上述两个表中实现 count(*) 的查询
mysql> select sum(tbl.EachTableCount) -> from -> ( -> select count(*) as EachTableCount from userdemo -> UNION ALL -> select count(*) as EachTableCount from wheredemo -> )tbl;
以下是输出 −
+-------------------------+ | sum(tbl.EachTableCount) | +-------------------------+ | 9 | +-------------------------+ 1 row in set (0.00 sec)
