distinct /count/ other aggrevate function for null value
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
-------------------------------------------------------------------------------------------------
select count( distinct 1) --affect 1 ,so 1 only
from (select '1' col
from dual
union all
select '1' col
from dual
union all
select '' col from dual); ---- distinct can not identify null -- 1
select count( distinct 1) --affect 1 ,so 1 only
from (select '1' col
from dual
union all
select '2' col ---1
from dual
union all
select '' col from dual); ---- distinct can not identify null -- 1
select count( 1) -- count (1) = count constantance so ,null record willi included
from (select '1' col
from dual
union all
select '1' col
from dual
union all
select '' col from dual); -- 3
---------------
select count(distinct col) --1。distinct return 2 records 2. actually count col ,is omit null value
from (select '1' col
from dual
union all
select '1' col
from dual
union all
select '' col from dual); -- 1
--------------
select count( col)
from (select '1' col
from dual
union all
select '1' col
from dual
union all
select '' col from dual); -- 2
--------------aggrevate fucntion omit null value ----------------------
select sum( col)
from (select '1' col
from dual
union all
select '1' col
from dual
union all
select '' col from dual); -- 2
select sum( distinct col)
from (select '1' col
from dual
union all
select '1' col
from dual
union all
select '' col from dual); -- 1
select sum( 1) --sum 的是1 ,有几条记录3
from (select '1' col
from dual
union all
select '1' col
from dual
union all
select '' col from dual); -- 3
select sum(distinct 1) ----sum 的是1 ,有几条记录在distinct 1 后,1条
from (select '1' col
from dual
union all
select '1' col
from dual
union all
select '' col from dual); -- 1
------------------
select min(col)
from (select '1' col
from dual
union all
select '1' col
from dual
union all
select '' col from dual); -- 1 max ,min are ignore the ''
-- MR_MGN_CALC
-------------------------- 2 records will return including null value as 1 group of them .
select distinct col
from (select '1' col
from dual
union all
select '1' col
from dual
union all
select '' col from dual)
group by col;
--------
select distinct col
from (select '1' col
from dual
union all
select '1' col
from dual
union all
select '' col from dual);
---so resutl is very strange .------
/* COL COUNT(1) COUNT(COL)
1 2 2
NULL 1 0
*/
select nvl(col,'NULL'),count(1),count(col)
from (select '1' col
from dual
union all
select '1' col
from dual
union all
select '' col from dual)
group by col;
select count('')
from (select '' col
from dual
union all
select '' col
from dual
union all
select '' col from dual)
group by col;
-------------distinct identify null--------------------
select distinct 1 --affect 1 ,so 1 only distinct 常量,永远都只有一条数据
from (select '1' col
from dual
union all
select '2' col
from dual
union all
select '' col from dual); ---- distinct can not identify null -- 1
select distinct col
from (select '1' col
from dual
union all
select '2' col
from dual
union all
select '' col from dual
union all
select '' col from dual); ---- distinct can not identify null -- 3
-----------count()-----------------
select count( col ),count(*),count(1)
from (select '1' col
from dual
union all
select '2' col
from dual
union all
select '' col from dual
union all
select '' col from dual); ---- distinct can not identify null -- 2,4,4
/* 1、在oracle中null就是null,不代表什么,什么也不代表。它充其量就像中国对台湾问题立场一样,我们保留使用武力解决台湾问题的权力。这里的null值也就代表我保留将给其重新赋一个值的权力。
2、除了count之外其它的聚合函数遇到null值,自动过滤null的记录。
3、与null任何四则运算都是null,当然有些函数是专门用来处理null的,比如nvl,nvl2,coalesce等等,当然decode,case也可以。
4、聚集函数中比较特殊的是COUNT,第一个特殊点是COUNT不会返回NULL值,即使表中没有记录,或者COUNT(COL)中,COL列的记录全为NULL,COUNT也会返回0值而不是NULL。第二个特殊点就是COUNT(*)或COUNT(常量)的形式。而像sum(null)的结果肯定就是null。
5、关于更多关于null的问题,可以查阅相关资料,在此不过抛了块烂砖希望能引出宝玉来。
6、建议在涉及计算时,如果该列或这些列没有not null约束,请一定要使用nvl(字段名,0)来代替原来的列,以防万一。
*/
/*大部分集合函数在进行计算时都去除NULL值,但COUNT函数是一个例外。
!!!一般情况下当对一个包含NULL值的列使用COUNT函数时,这个列中的NULL值将会被去除掉。
!!!但是如果COUNT函数使用一个星号or 常量,它就会计算所有的行,而不管其中是否包含NULL值。*/
/*
!!!! So Count ,better 'X'
!!!! aggregate fucntion ,using nvl*/
posted on 2009-06-20 03:52 dolphin_bobo 阅读(244) 评论(0) 编辑 收藏 举报