使用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,0)) 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>0,c.couponid,null)) as use_coupon_num_real,
           count(distinct if(c.couponid is not null and c.realprice>0,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/1000)))='2016-12-14' and
                to_date( from_unixtime(int(createdtime /1000)))=date_sub('2016-12-14',7)
                and appkey='3'
                )a
            left join
                (
                select case when source='10' then 'hh' when source='20' then 'aa' when source='30' then 'cc'
                when source='21' then 'dd' when source='31' then 'ee' else 'others' end as souform,
                       if(fee-discount-coupon_price>0,fee-discount-coupon_price,0)as realprice,couponid,coupon_price,userid
                from tutor.ods_tutor_order
                where to_date(from_unixtime(int( paidtime/1000))) between date_sub('2016-12-14',7) and '2016-12-14'
                )b
            on a.id=b.couponid
  ) c
            group by c.value,c.souform with cube

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

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

 

转载于:https://www.cnblogs.com/songjiaying/p/6182481.html