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

本文探讨了在SQL查询中使用COALESCE函数结合GROUP BY with cube时可能出现的问题,特别是当左连接(left join)的表中有NULL值时,可能导致主键不唯一的情况。通过具体案例展示了如何识别这一问题,并提供了查询语句供读者参考。

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

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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值