选择非重复计数(id)与选择非重复计数(id不同) [英] select distinct count(id) vs select count(distinct id)

问题描述

我正在尝试从表中获得不同的值.当我运行select distinct count(id) from table时，我得到了超过一百万的计数.但是，如果我运行select count(distinct id) from table，我只能得到大约30万个计数.这两个查询有什么区别?

I'm trying to get distinct values from a table. When I ran select distinct count(id) from table I got over a million counts. However if I ran select count(distinct id) from table I've got only around 300k counts. What was the difference of the two queries?

谢谢

推荐答案

当您执行select distinct count(id)时，您基本上是在做:

When you do select distinct count(id) then you are basically doing:

select distinct cnt
from (select count(id) as cnt from t) t;

因为内部查询只返回一行，所以distinct没有做任何事情.该查询对表中的行数进行计数(更准确地说，是id不是null的行数).

Because the inner query only returns one row, the distinct is not doing anything. The query counts the number of rows in the table (well, more accurately, the number of rows where id is not null).

另一方面，当您这样做时:

On the other hand, when you do:

select count(distinct id)
from t;

然后查询计算表中id所采用的不同值的数量.这似乎就是您想要的.

Then the query counts the number of different values that id takes on in the table. This would appear to be what you want.

这篇关于选择非重复计数(id)与选择非重复计数(id不同)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！