distinct /count/ other aggrevate function for null value-优快云博客

本文详细解析了SQL中不同计数函数的行为差异，特别是如何处理空值（NULL）。探讨了COUNT、SUM等聚合函数在面对NULL时的不同表现，并通过实例展示了DISTINCT关键字的应用技巧。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Code
-------------------------------------------------------------------------------------------------

        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*/

转载于:https://www.cnblogs.com/mlaaalm/archive/2009/06/20/1507197.html