UC知道sql多表汇总，值丢失
来源：百度知道 编辑：UC知道 时间：2024/06/07 00:48:11
麻烦看下以下语句错在哪里？value1和value2得到的总是空值。。。
但若单独查询第二张表的这两个汇总值又会得到正确值
select
1 type_id,
COUNT(DISTINCT a.SERV_ID) VALUE1,
sum(decode(b.ACCT_ITEM_TYPE_ID,'52507',b.CHARGE,0)) VALUE2,
sum(decode(b.ACCT_ITEM_TYPE_ID,'52510',b.CHARGE,0)) VALUE3
from
(select * from channel_chengdu.f_3_GOLDCAMPUS_serv_m partition(p200904)
where comp_suit_type_id=92 and prodf_flag=0 and comp_prod_flag=1
and term_type_id=75 and substr(state_f,3) in ('A','N','K','Z')) a,
(select * from channel_chengdu.f_3_GOLDCAMPUS_acct_item_m partition(p200904)
where ACCT_ITEM_TYPE_ID in ('52507','52510')) b;
但若单独查询第二张表的这两个汇总值又会得到正确值
select
1 type_id,
COUNT(DISTINCT a.SERV_ID) VALUE1,
sum(decode(b.ACCT_ITEM_TYPE_ID,'52507',b.CHARGE,0)) VALUE2,
sum(decode(b.ACCT_ITEM_TYPE_ID,'52510',b.CHARGE,0)) VALUE3
from
(select * from channel_chengdu.f_3_GOLDCAMPUS_serv_m partition(p200904)
where comp_suit_type_id=92 and prodf_flag=0 and comp_prod_flag=1
and term_type_id=75 and substr(state_f,3) in ('A','N','K','Z')) a,
(select * from channel_chengdu.f_3_GOLDCAMPUS_acct_item_m partition(p200904)
where ACCT_ITEM_TYPE_ID in ('52507','52510')) b;
任何值和null值做运算出来的都是null
sum(decode(b.ACCT_ITEM_TYPE_ID,'52507',b.CHARGE,0)) VALUE2,
sum(decode(b.ACCT_ITEM_TYPE_ID,'52510',b.CHARGE,0)) VALUE3
改成
sum(decode(nvl(b.ACCT_ITEM_TYPE_ID,'0'),'52507',b.CHARGE,0)) VALUE2,
sum(decode(nvl(b.ACCT_ITEM_TYPE_ID,'0'),'52510',b.CHARGE,0)) VALUE3
---补充
刚才错了
改成
sum(decode(b.ACCT_ITEM_TYPE_ID,'52507',nvl(b.CHARGE,0),0)) VALUE2,
sum(decode(b.ACCT_ITEM_TYPE_ID,'52510',nvl(b.CHARGE,0),0)) VALUE3