使用COALESCE时注意left join为null的情况

1.使用COALESCE时，用到group by with cube,如果之前两个表left join时，有数据为null，就会使得查出的数据主键不唯一

例如：

select

           COALESCE (c.value,'total_count')as coupon_price,

           COALESCE (c.souform,'total_count')as souform,

           count(c.id)as coupon_num,

           count(distinct c.auserid,c.code)as user_num,

           sum(if(c.realprice is not null,c.realprice,)) as realprice,

           count(if(c.couponid is not null,c.couponid,null)) as use_coupon_num,

           count(distinct if(c.couponid is not null,c.buserid,null))as use_user_num,

           count(distinct if(c.couponid is not null and c.realprice>,c.couponid,null)) as use_coupon_num_real,

           count(distinct if(c.couponid is not null and c.realprice>,c.buserid,null)) as user_coupon_num_real

           from

(

select a.value ,b.souform,a.id,a.userid as auserid,a.code,b.realprice,b.couponid,b.userid as buserid

              from

                (s

                select id,userid,code,value

                from tutor.ori_mysql_tutor_coupon_coupon_da

                where to_date( from_unixtime(int(expiredtime/)))='2016-12-14' and

                to_date( from_unixtime(int(createdtime /)))=date_sub('2016-12-14',)

                and appkey=''

                )a

            left join

                (

                select case when source='' then 'hh' when source='' then 'aa' when source='' then 'cc'

                when source='' then 'dd' when source='' then 'ee' else 'others' end as souform,

                       if(fee-discount-coupon_price>,fee-discount-coupon_price,)as realprice,couponid,coupon_price,userid

                from tutor.ods_tutor_order

                where to_date(from_unixtime(int( paidtime/))) between date_sub('2016-12-14',) and '2016-12-14'

                )b

            on a.id=b.couponid

  ) c

            group by c.value,c.souform with cube

得到的数据会出现两组key一样但数据不一样的情况：

说明其中第二个数据是a表中没有匹配到souform的空值