来自 MySQL 中多个表的 COUNT(*)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3761240/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
COUNT(*) from multiple tables in MySQL
提问by bcmcfc
How do I go about selecting COUNT(*)s from multiple tables in MySQL?
如何从 MySQL 的多个表中选择 COUNT(*)s？
Such as:
如：
SELECT COUNT(*) AS table1Count FROM table1 WHERE someCondition
JOIN??
SELECT COUNT(*) AS table2Count FROM table2 WHERE someCondition
CROSS JOIN? subqueries?
SELECT COUNT(*) AS table3Count FROM table3 WHERE someCondition
Edit:
编辑：
The goal is to return this:
目标是返回这个：
+-------------+-------------+-------------+
| table1Count | table2Count | table3Count |
+-------------+-------------+-------------+
| 14 | 27 | 0 |
+-------------+-------------+-------------+
回答by Julien Hoarau
You can do it by using subqueries, one subquery for each tableCount :
您可以通过使用子查询来实现，每个 tableCount 一个子查询：
SELECT
(SELECT COUNT(*) FROM table1 WHERE someCondition) as table1Count,
(SELECT COUNT(*) FROM table2 WHERE someCondition) as table2Count,
(SELECT COUNT(*) FROM table3 WHERE someCondition) as table3Count
回答by RedFilter
You can do this with subqueries, e.g.:
您可以使用子查询执行此操作，例如：
select (SELECT COUNT(*) FROM table1 WHERE someCondition) as table1Count,
(SELECT COUNT(*) FROM table2 WHERE someCondition) as table2Count
回答by Interfector
You can use UNION
你可以使用联合
SELECT COUNT(*) FROM table1 WHERE someCondition
UNION
SELECT COUNT(*) FROM table2 WHERE someCondition
UNION
SELECT COUNT(*) FROM table3 WHERE someCondition
回答by Pramendra Gupta
Try changing to:
尝试更改为：
SELECT
COUNT(table1.*) as t1,
COUNT(table2.*) as t2,
COUNT(table3.*) as t3
FROM table1
LEFT JOIN tabel2 ON condition
LEFT JOIN tabel3 ON condition
回答by S3DEV
Here is simple approach to get purely the row counts from multiple tables, if there are no conditions on specific tables.
如果特定表没有条件，这里是从多个表中纯粹获取行数的简单方法。
Note:
笔记：
For InnoDB this count is an approximation. However, for MyISAM the count is accurate.
对于 InnoDB，这个计数是一个近似值。但是，对于 MyISAM，计数是准确的。
Quoted from the docs:
引用自文档：
The number of rows. Some storage engines, such as MyISAM, store the exact count. For other storage engines, such as InnoDB, this value is an approximation, and may vary from the actual value by as much as 40% to 50%. In such cases, use SELECT COUNT(*) to obtain an accurate count.
行数。一些存储引擎，例如 MyISAM，存储确切的计数。对于其他存储引擎，比如 InnoDB，这个值是一个近似值，可能与实际值相差 40% 到 50%。在这种情况下，请使用 SELECT COUNT(*) 获得准确的计数。
Using the information_schema.tablestable you can use:
使用该information_schema.tables表，您可以使用：
SELECT
table_name,
table_rows
FROM
information_schema.tables
WHERE
table_name like 'my_table%';
Output:
输出：
table_name table_rows
my_table_1 0
my_table_2 15
my_table_3 30
I'm posting this answer because I ran across this post in search of this method, which matches the title of the post. Hopefully will help someone in the future.
我发布这个答案是因为我在搜索这个与帖子标题相匹配的方法时遇到了这个帖子。希望将来会帮助某人。
