有人能解释Count在这个SQL查询中是如何工作的吗?

时间:2021-09-27 01:31:48

I'm making a table in SSRS that contains customer names in column 1 and their corresponding number of orders in column 2. This query works for what I'm trying to accomplish, but I don't know exactly how the Count function knows what the heck I want it to count and what table I'm wanting it to count from. Could someone please explain this to me so I can better understand in the future? Thanks a ton.

我在SSRS中创建了一个表,其中包含列1中的客户名和列2中相应的订单数量。这个查询适用于我要完成的任务,但是我不知道Count函数是如何知道我要它去计数的,以及我希望它从哪个表计数的。能不能给我解释一下,以便我以后能更好的理解?由于一吨。

SELECT Customers.name
    ,Count(1) AS OrderCount
FROM Customers
INNER JOIN Orders
    ON Customers.id = Orders.customer_id
GROUP BY Customers.name

3 个解决方案

#1


6  

I don't know exactly how the Count function knows what the heck I want it to count

我不知道计数函数是怎么知道我要它计数的

There is only one thing that COUNT is able to count - it can count rows in which an expression evaluates to a non-null value. If you use COUNT(1) in a regular query, you would get 1 on each row. With GROUP BY, however, the COUNT will return the number of rows in the specific group. In your case, that would be the number of rows with the same Customers.name, because that is what you use for GROUP BY.

COUNT只有一件事是可以计数的——它可以计数表达式计算为非空值的行。如果在常规查询中使用COUNT(1),那么每一行就会得到1。然而,对于GROUP BY,计数将返回特定组中的行数。在您的示例中,这将是具有相同的Customers.name的行数,因为这是您用于GROUP BY的。

As far as passing 1 to COUNT goes, a more common practice these days is to pass an asterisk, i.e. to write COUNT(*), because in most RDBMS engines there is no performance penalty for that.

至于传递1到COUNT,现在更常见的做法是传递一个星号,也就是写入COUNT(*),因为在大多数RDBMS引擎中,这没有性能损失。

#2


2  

Count is counting true for each record found. Therefore, if there are 3 records, it is counting true 3 times and returns 3. It doesn't really matter what it is counting in there as long as it exists or is a constant. If it exists, it is counted. It's the number of rows that you are being grouped when you group by that matters.

计数是为每个发现的记录计数为真。因此,如果有3条记录,它将计数true 3次并返回3。它的值是多少并不重要,只要它存在或者是一个常数。如果它存在,就被计算。重要的是你分组时被分组的行数。

#3


-3  

it counts number of occurences of the first field in the result set

它计算结果集中第一个字段出现的次数

#1


6  

I don't know exactly how the Count function knows what the heck I want it to count

我不知道计数函数是怎么知道我要它计数的

There is only one thing that COUNT is able to count - it can count rows in which an expression evaluates to a non-null value. If you use COUNT(1) in a regular query, you would get 1 on each row. With GROUP BY, however, the COUNT will return the number of rows in the specific group. In your case, that would be the number of rows with the same Customers.name, because that is what you use for GROUP BY.

COUNT只有一件事是可以计数的——它可以计数表达式计算为非空值的行。如果在常规查询中使用COUNT(1),那么每一行就会得到1。然而,对于GROUP BY,计数将返回特定组中的行数。在您的示例中,这将是具有相同的Customers.name的行数,因为这是您用于GROUP BY的。

As far as passing 1 to COUNT goes, a more common practice these days is to pass an asterisk, i.e. to write COUNT(*), because in most RDBMS engines there is no performance penalty for that.

至于传递1到COUNT,现在更常见的做法是传递一个星号,也就是写入COUNT(*),因为在大多数RDBMS引擎中,这没有性能损失。

#2


2  

Count is counting true for each record found. Therefore, if there are 3 records, it is counting true 3 times and returns 3. It doesn't really matter what it is counting in there as long as it exists or is a constant. If it exists, it is counted. It's the number of rows that you are being grouped when you group by that matters.

计数是为每个发现的记录计数为真。因此,如果有3条记录,它将计数true 3次并返回3。它的值是多少并不重要,只要它存在或者是一个常数。如果它存在,就被计算。重要的是你分组时被分组的行数。

#3


-3  

it counts number of occurences of the first field in the result set

它计算结果集中第一个字段出现的次数