有一个表名为com 表字段和内容如下
id name bols
1 a 40
2 b 50
3 c
4 d
现问:select bols ,count(bols) from com group by bols;
为什么会输出40 1
50 1
0,我想的是,在输出结果为空时,应该是为3的呀,
大家帮我看下为什么,thanks
id name bols
1 a 40
2 b 50
3 c
4 d
现问:select bols ,count(bols) from com group by bols;
为什么会输出40 1
50 1
0,我想的是,在输出结果为空时,应该是为3的呀,
大家帮我看下为什么,thanks
和空。
但:
COUNT(*) 返回组中项目的数量,这些项目包括 NULL 值和副本。COUNT(ALL expression) 对组中的每一行都计算 expression 并返回非空值的数量。COUNT(DISTINCT expression) 对组中的每一行都计算 expression 并返回唯一非空值的数量。
WITH t AS (
SELECT 'a'NAME,40 bols FROM dual
UNION ALL
SELECT 'b',50 FROM dual
UNION ALL
SELECT 'c',NULL FROM dual
UNION ALL
SELECT 'd',NULL FROM dual
)
SELECT bols,Count(Nvl(bols,0)) FROM t GROUP BY bols;
1 count(null)只返回0,为什么只返回0?
2 这样写可以吗,select bols ,count(nvl(bols,' ')) from com group by nvl(bols,' ');
1、没法计算null
2、select nvl(bols,' ') ,count(nvl(bols,' ')) from com group by nvl(bols,' ');