mysql How to COUNT() IFNULL字段+IFNULL

Question

字段版本可以有null，但我也需要计算这样的行数。我尝试这样做: IFNULL( count ( version )，1)，但不是work

select MIN(id) as id, version, IFNULL(COUNT(version), 1) as total
from hlrlookup.hlrlookup
WHERE create_timestamp > '2015-02-01 05:00:00' and create_timestamp < '2016-04-28 06:00:00'
group by version;

帮助

Answer 1

COUNT()从不返回NULL，而是返回0。如果要计算所有行的数量，则使用COUNT(*)

select MIN(id) as id, version, COUNT(*) as total
from hlrlookup.hlrlookup
WHERE create_timestamp > '2015-02-01 05:00:00' and
      create_timestamp < '2016-04-28 06:00:00'
GROUP BY version;

编辑：

我要指出的是，表达您试图做的逻辑的正确方式是：

select MIN(id) as id, version, GREATEST(COUNT(version), 1) as total
. . .

Answer 2

在group by语句中有version。您可以只对每个版本的行求和：

select MIN(id) as id, version, SUM(1) as total
from hlrlookup.hlrlookup
WHERE create_timestamp > '2015-02-01 05:00:00' and create_timestamp < '2016-04-28 06:00:00'
group by version;