SQL中的count(1)是什么意思?

问题描述：

select patientID, count(1)    
from dbo (nolock)    
where admissiontime between '2020-01-31' and '2020-02-01'

patientID in (0, 1)

/*
0 means arthritis

1 means asthma
*/

因此，以上查询用于查找医院针对哮喘和关节炎相关病例提出的要求.

So above query is used to find how many times there was a request from hospital for both asthma and arthritis related case.

它按 PatientID 放在一个组中，但是 count(1)是否表示它计算了所有PatientID?

It is put in a group by the patientID, but does that count(1) mean it counts everything patientID?

答

基本上， count(1)产生的结果与 count(*)相同:，它计算由 group by 子句定义的组中的记录数.

Basically, count(1) produces just the same result as count(*): that is, it counts the number of records in the group defined by the group by clause.

为什么? count(< expr>)对< expr> 的每个非 null 值进行计数.这里给它一个常量值 1 ，它永远不会为 null -因此它计算所有行.

Why? count(<expr>) counts every non-null value of <expr>. Here it is given a constant value, 1, that is never null - so it counts all rows.

我不推荐 count(1)；检查每一行是否为空需要仅计算所有行的数据库工作.一些数据库可能会将 count(1)优化为 count(*)(显然，此固定表达式永远不会为 null )，但是当您可以优化自己的时候又何必呢?只需始终使用 count(*).

I wouldn't recommend count(1); checking for nullity of each and every row requires more work for the database that just counting all rows. Some databases might optimize count(1) as count(*) under the hood (as it is obvious that this fixed expression will never be null), but why bother, when you can optimize yourself already? Just use count(*) consistently.

SQL中的count(1)是什么意思?

相关推荐