计数(*)多个表

Question

我正在浏览从多个表获取计数的解决方案，我遇到了以下答案：

SELECT COUNT(*),(SELECT COUNT(*) FROM table2) FROM table1

它效果很好，但我似乎无法让它适用于超过 2 个表。我当前的代码如下：

SELECT COUNT(*), 
(SELECT COUNT(*) FROM TABLE1),
(SELECT COUNT(*) FROM TABLE2),
(SELECT COUNT(*) FROM TABLE3),
(SELECT COUNT(*) FROM TABLE4),
(SELECT COUNT(*) FROM TABLE5),
(SELECT COUNT(*) FROM TABLE6),
(SELECT COUNT(*) FROM TABLE7),
(SELECT COUNT(*) FROM TABLE8),
(SELECT COUNT(*) FROM TABLE9),
(SELECT COUNT(*) FROM TABLE10),
(SELECT COUNT(*) FROM TABLE11),
(SELECT COUNT(*) FROM TABLE12),
(SELECT COUNT(*) FROM TABLE13),
(SELECT COUNT(*) FROM TABLE14),
(SELECT COUNT(*) FROM TABLE15),
(SELECT COUNT(*) FROM TABLE16),
(SELECT COUNT(*) FROM TABLE17),
(SELECT COUNT(*) FROM TABLE18)
FROM TABLE19

但是，它只计算TABLE1和TABLE19。我需要计算所有表（表 1-18）以及表 19（希望使用与第一个示例类似的结构）。

Answer 1

根据哪个数据库，它可能会略有变化...

对于Oracle，请执行以下操作：

Select (select count(*) from table1) as table1Count, 
       (select count(*) from table2) as table2Count
from dual

如果是SQL Server，则不要使用dual。

编辑：

既然您在评论中提到您正在使用MySQL：

获取MySQL数据库中所有表的记录数

Answer 2

使用别名使列具有唯一的名称：

SELECT 
    (SELECT COUNT(*) FROM TABLE1) AS count_table1,
    (SELECT COUNT(*) FROM TABLE2) AS count_table2,
    (SELECT COUNT(*) FROM TABLE3) AS count_table3,
    etc..
    (SELECT COUNT(*) FROM TABLE19) AS count_table19

Answer 3

如果您愿意接受行而不是列中的结果，您可以随时使用 UNION：

SELECT "table1", COUNT(*) FROM table1 UNION                         
SELECT "table2", COUNT(*) FROM table2 UNION                         
SELECT "table3", COUNT(*) FROM table3

等等。

Answer 4

在 MySQL 上，这是有效的：

select sum(total) from(
select count(*) as total from Table1 
union
select count(*) as total from Table2) as a;