SELECT COUNT（*）; [英] SELECT COUNT(*) ;

问题描述

我有一个数据库， database1 ，有两个表（表1 ， Table2 ）。

Table1 中有3行， Table2中有2行。现在如果我在上执行以下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）
）
  

SELECT [columns，列上的标量计算，列上的分组计算或标量计算] FROM [表或连接表格等]

因为这允许简单的标量计算，我们可以做 SELECT 1 + 1 FROM SomeTable ，它将为表 SomeTable 中的每一行返回值为2的记录集。

现在，如果我们不关心任何表，但只是想做我们的标量计算，我们可能想做一些像 SELECT 1 + 1 。这是标准不允许的，但它是有用的，并且大多数数据库允许它（Oracle不会，除非最近更改，至少它不是）。

因此，这种裸的SELECT被视为具有from子句，它指定了一行且没有列的表（当然是不可能的，但它的作用）。因此， SELECT 1 + 1 变为 SELECT 1 + 1 FROM ImaginaryTableWithOneRow ，它返回一个单列 2 。

大多数情况下，我们不考虑这个，我们只是习惯了裸SELECT

在中选择COUNT （*）你相当于 SELECT COUNT（*）FROM ImaginaryTableWithOneRow ，当然返回1。

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)
)

解决方案

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 SomeTable and it will return a recordset with the value 2 for every row in the table SomeTable.

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 + 1 becomes SELECT 1 + 1 FROM ImaginaryTableWithOneRow which returns a single row with a single column with the value 2.

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 of SELECT COUNT(*) FROM ImaginaryTableWithOneRow which of course returns 1.

这篇关于SELECT COUNT（*）;的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！