oracle SQL 查询语法：在计数中使用表别名无效？为什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2906017/
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
SQL Query Syntax : Using table alias in a count is invalid? Why?
提问by contactmatt
Could someone please explain to me why the following query is invalid? I'm running this query against an Oracle 10g database.
有人可以向我解释为什么以下查询无效吗？我正在针对 Oracle 10g 数据库运行此查询。
select count(test.*) from my_table test;
I get the following error: ORA-01747: invalid user.table.column, table.column, or column specification
我收到以下错误：ORA-01747：无效的 user.table.column、table.column 或列规范
however, the following two queries are valid.
但是，以下两个查询是有效的。
select count(test.column) from my_table test;
select test.* from my_table test;
回答by Vincent Malgrat
COUNT(expression)will count all rows where expressionis not null. COUNT(*)is an exception, it returns the number of rows: *is not an alias for my_table.*.
COUNT(expression)将计算所有expression不为空的行。COUNT(*)是一个例外，它返回行数：*不是 的别名my_table.*。
回答by Thomas
As far as I know, Count(Table.*)is not officially supported in the SQL specification. Only Count(*)(count all rows returned) and Count(Table.ColumnName)(count all non-null values in the given column). So, even if the DBMS supported it, I would recommend against using it.`
据我所知，Count(Table.*)在 SQL 规范中没有正式支持。仅Count(*)（计算返回的所有行）和Count(Table.ColumnName)（计算给定列中的所有非空值）。因此，即使 DBMS 支持它，我也建议不要使用它。`
回答by Michael Streeter
You might reasonably want to find the number of records where test.column is not NULL if you are doing an outer join. As every table should have a PK (which is not null) you should be able to count the rows like that if you want:
如果您正在执行外连接，您可能有理由希望找到 test.column 不为 NULL 的记录数。由于每个表都应该有一个 PK（不为空），如果需要，您应该能够像这样计算行数：
select count(y.pk)
from x
left outer join y on y.pk = x.ck
COUNT(*) is no good here because the outer join is creating a null row for the table that is deficient in information.
COUNT(*) 在这里不好用，因为外连接正在为信息不足的表创建一个空行。
回答by Quassnoi
This syntax only works in PostgreSQLand only because it has a record datatype (for which test.*is a meaningful expression).
此语法仅适用于PostgreSQL且仅因为它具有记录数据类型（对于它来说test.*是一个有意义的表达式）。
Just use COUNT(*).
只需使用COUNT(*).
This query:
这个查询：
select count(test.column) from my_table test;
will return you the number of records for which test.columnis not NULL.
将返回您test.column不是的记录数NULL。
This query:
这个查询：
select test.* from my_table test;
will just return you all records from my_table.
只会返回你所有的记录my_table。
COUNTas such is probably the only aggregate that makes sense without parameters, and using an expression like COUNT(*)is just a way to call a function without providing any actual parameters to it.
COUNT因此可能是唯一一个没有参数有意义的聚合，并且使用 like 表达式COUNT(*)只是一种调用函数而不提供任何实际参数的方法。
