有和没有GROUP BY的COUNT(*)，没有匹配的行 [英] COUNT(*) with and without GROUP BY, no matching rows

问题描述

考虑具有以下记录的关系表(Balance，Customer):

Consider a relation table (Balance,Customer) with the following records:

现在，我尝试了这两个查询:

-- Query 1:
select A.Customer, count(B.Customer)
from account A, account B
where A.balance < B.balance
group by A.Customer;

-- Query 2:
select A.Customer, count(B.Customer)
from account A, account B
where A.balance < B.balance;

第一个查询没有任何输出.在第二个查询中，我得到了count = 0的输出.

The first query gives me no output. With the second query, I am getting an output with count = 0.

在两种情况下，都没有满足where子句中条件的行，因此不返回任何行.那么为什么count函数仅在第二种情况下给出输出?

In both cases there are no rows satisfying the criteria in the where clause, and hence no rows are returned. Then why is the count function giving an output only in the second case?

推荐答案

不具有group by 始终的聚合查询返回一行(如果从语法上讲是正确的).这样的行数将为0.

An aggregation query that has no group by always returns one row (if it is syntactically correct). The count in such a row would be 0.

使用group by的聚合查询每组返回一行.如果没有组，那么就没有行.

An aggregation query with a group by returns one row per group. If there are no groups then there are no rows.

这篇关于有和没有GROUP BY的COUNT(*)，没有匹配的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！