问题

我使用的是Firebird 2.5，以下是代码：

SELECT r.RDB$RELATION_NAME AS TABLE_NAME, COUNT(*) AS TOTAL_ROWS
FROM RDB$RELATIONS r
JOIN RDB$RELATION_FIELDS f ON r.RDB$RELATION_NAME = f.RDB$RELATION_NAME
GROUP BY r.RDB$RELATION_NAME;

有一张名为"customer"的表，上述代码返回总行数 = 332行。

如果我运行：

SELECT COUNT(*) FROM customer;

结果返回总行数 = 10000++ 行。

在第一个SQL语句中，我漏掉了什么？

SELECT r.RDB$RELATION_NAME AS TABLE_NAME, COUNT(*) AS TOTAL_ROWS
FROM RDB$RELATIONS r
JOIN RDB$RELATION_FIELDS f ON r.RDB$RELATION_NAME = f.RDB$RELATION_NAME
GROUP BY r.RDB$RELATION_NAME;

SELECT COUNT(*) FROM customer;

答案1

得分: 1

The first statement returns the number of fields (columns) because RDB$RELATION_FIELDS stores the definitions of table and view columns.

第一条语句返回字段（列）的数量，因为RDB$RELATION_FIELDS存储了表和视图列的定义。

The second returns the number of rows (the data).

第二条语句返回行数（数据）的数量。

As I understand you want to get the number of rows (data) in all tables. If so you can use:

根据我理解，您想要获取所有表中的行数（数据）。如果是这样，您可以使用：

How to get record count for all tables?:

如何获取所有表的记录数？:

> Sometimes, when inspecting a database, you might want to get a quick glance over all the tables to see which ones actually have some data in them. There is no quick and easy way to get rough estimates, but you can always run brute-force "select count(*)" query on each table. Combining EXECUTE STATEMENT and EXECUTE BLOCK you can do this in a single SQL command:

> 有时，在检查数据库时，您可能希望快速查看所有表，以查看哪些实际上包含了一些数据。没有快速且简便的方式来获得粗略的估算，但您始终可以在每个表上运行“select count(*)”查询。通过结合使用EXECUTE STATEMENT和EXECUTE BLOCK，您可以在单个SQL命令中执行此操作：

set term !! ;

EXECUTE BLOCK
returns ( stm varchar(60), cnt integer )
as
BEGIN
for select cast('select count(*) from "'||trim(r.RDB$RELATION_NAME)||'"' as varchar(60))
from RDB$RELATIONS r
where (r.RDB$SYSTEM_FLAG is null or r.RDB$SYSTEM_FLAG = 0) and r.RDB$VIEW_BLR is null
order by 1
into :stm
DO
BEGIN
execute statement :stm into :cnt;
suspend;
END
END

set term !! ;
EXECUTE BLOCK
returns ( stm varchar(60), cnt integer )
as
BEGIN
for select cast('select count(*) from "'||trim(r.RDB$RELATION_NAME)||'"' as varchar(60))
from RDB$RELATIONS r
where (r.RDB$SYSTEM_FLAG is null or r.RDB$SYSTEM_FLAG = 0) and r.RDB$VIEW_BLR is null
order by 1
into :stm
DO
BEGIN
execute statement :stm into :cnt;
suspend;
END
END

答案2

得分: 0

你正在查询元数据表，该表提供有关数据库对象的结构和定义的信息。表RDB$RELATIONS包含表和视图，表RDB$RELATION_FIELDS包含表和视图列的定义。

如果第一个查询返回CUSTOMERS的列数为332，这意味着表CUSTOMERS有332个列，这并不反映表CUSTOMERS中存储的行数。