选择计数（*）；
我有一个数据库 database1，其中有两个表（Table 1、Table2）。
Table1 中有 3 行，Table2 中有 2 行。现在，如果我在 database1 上执行以下 SQL 查询 SELECT COUNT(*);，则输出为 "1"。
有谁知道这个“1”表示什么？
两个表的定义如下。
CREATE TABLE Table1
(
ID INT PRIMARY KEY,
NAME NVARCHAR(20)
)
CREATE TABLE Table2
(
ID INT PRIMARY KEY,
NAME NVARCHAR(20)
)
I have a database, database1, with two tables (Table 1, Table2) in it.
There are 3 rows in Table1 and 2 rows in Table2. Now if I execute the following SQL query SELECT COUNT(*); on database1, then the output is "1".
Does anyone has the idea, what this "1" signifies?
The definition of the two tables is as below.
CREATE TABLE Table1
(
ID INT PRIMARY KEY,
NAME NVARCHAR(20)
)
CREATE TABLE Table2
(
ID INT PRIMARY KEY,
NAME NVARCHAR(20)
)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（8）
通常，所有选择的形式都是 SELECT [列、列上的标量计算、列上的分组计算或标量计算] FROM [表或表的连接等]
因为这允许简单的标量计算，我们可以执行类似
SELECT 1 + 1 FROM SomeTable的操作，它将返回表SomeTable中每一行的值为 2 的记录集。现在，如果我们不关心任何表，而只想进行标量计算，我们可能需要执行诸如
SELECT 1 + 1之类的操作。标准不允许这样做，但它很有用，而且大多数数据库都允许（Oracle 不允许，除非最近更改，至少以前不允许）。因此，这样的裸 SELECT 被视为好像它们有一个 from 子句，该子句指定一个只有一行且没有列的表（当然不可能，但它确实有效）。因此，
SELECT 1 + 1变为SELECT 1 + 1 FROM ImaginaryTableWithOneRow，它返回单行和单列，其值为2。大多数情况下，我们不会考虑这一点，我们只是习惯了裸露的 SELECT 给出结果的事实，甚至没有考虑必须选择一些单行内容来返回一行的事实。
在执行
SELECT COUNT(*)时，您执行了与SELECT COUNT(*) FROM ImaginaryTableWithOneRow等效的操作，当然会返回 1。Normally all selects are of the form
SELECT [columns, scalar computations on columns, grouped computations on columns, or scalar computations] FROM [table or joins of tables, etc]Because this allows plain scalar computations we can do something like
SELECT 1 + 1 FROM SomeTableand it will return a recordset with the value 2 for every row in the tableSomeTable.Now, if we didn't care about any table, but just wanted to do our scalar computed we might want to do something like
SELECT 1 + 1. This isn't allowed by the standard, but it is useful and most databases allow it (Oracle doesn't unless it's changed recently, at least it used to not).Hence such bare SELECTs are treated as if they had a from clause which specified a table with one row and no column (impossible of course, but it does the trick). Hence
SELECT 1 + 1becomesSELECT 1 + 1 FROM ImaginaryTableWithOneRowwhich returns a single row with a single column with the value2.Mostly we don't think about this, we just get used to the fact that bare SELECTs give results and don't even think about the fact that there must be some one-row thing selected to return one row.
In doing
SELECT COUNT(*)you did the equivalent ofSELECT COUNT(*) FROM ImaginaryTableWithOneRowwhich of course returns 1.类似地，下面也返回一个结果。
该行为的解释（来自 此 Connect 项目）也适用于你的问题。
Along similar lines the following also returns a result.
The explanation for that behavior (from this Connect item) also applies to your question.
这是因为您执行了
select count(*)而没有指定表。count 函数返回指定数据集中的行数。如果您没有指定要从中选择的表，则单个选择将仅返回一行 - 因此
count(*)将返回 1。（在某些版本的 SQL 中，例如 Oracle，您必须指定一个表或类似的数据库对象；Oracle 包含一个虚拟表（称为 DUAL），当不需要特定表时可以选择该表。）It's because you have executed
select count(*)without specifying a table.The count function returns the number of rows in the specified dataset. If you don't specify a table to select from, a single select will only ever return a single row - therefore
count(*)will return 1. (In some versions of SQL, such as Oracle, you have to specify a table or similar database object; Oracle includes a dummy table (called DUAL) which can be selected from when no specific table is required.)如果不指定要查询的表，通常不会执行 select count(*)。您的数据库服务器可能根据它正在查询的默认系统表为您提供计数“1”。
尝试使用
没有表名，这是没有意义的。
you wouldn't normally execute a select count(*) without specifying a table to query against. Your database server is probably giving you a count of "1" based on default system table it is querying.
Try using
Without a table name it makes no sense.
没有表名它总是返回 1 无论它是任何数据库....
without table name it always return 1 whether it any database....
由于它被标记为 SQL Server，因此 MSDN 指出。
还，
因此，由于您没有提供用于执行
COUNT的表，默认（假设）是它返回1。Since this is tagged SQL server, the MSDN states.
Also,
Thus, since you didn't provide a table to do a
COUNTfrom, the default (assumption) is that it returns a1.COUNT函数返回结果行数。如果不指定任何表，则默认返回1。即，COUNT(*)、COUNT(1)、COUNT(2)、...将始终返回1。COUNTfunction returns the number of rows as result. If you don't specify any table, it returns 1 by default. ie.,COUNT(*), COUNT(1), COUNT(2), ...will return1always.没有 from 子句是“从宇宙中选择所有”，因为您没有过滤掉任何内容。
就你而言，你问“有多少个宇宙？”
这正是我要教的。第一天我会在黑板上写下：
选择*并询问它的含义。答：给我整个世界。
从那里我会教如何将宇宙过滤成有意义的东西。
我必须承认，我从来没有想过 Select Count(*)，这会让它变得更有趣，但仍然会带回一个真实的答案。我们只有一个世界。
如果不咨询史蒂文·霍金，SQL 将不得不与只有 1 的情况进行竞争。
查询的结果是正确的。
without a from clause is "Select ALL from the Universe" since you have filtered out nothing.
In your case, you are asking "How many universe?"
This is exactly how I would teach it. I would write on the board on the first day,
Select * and ask what it means. Answer: Give me the world.
And from there I would teach how to filter the universe down to something meaningful.
I must admit, I never thought of Select Count(*), which would make it more interesting but still brings back a true answer. We have only one world.
Without consulting Steven Hawking, SQL will have to contend with only 1.
The results of the query is correct.