语句如下
select * from (
select coalesce(t1.period_date,t2.period_date) period_date
,coalesce(t1.statistical_date,t2.statistical_date) statistical_date
,coalesce(t1.index_name,t2.index_name) index_name
,coalesce(t1.budget_name,t2.budget_name) budget_name
,coalesce(t1.trade_regname,t2.trade_regname) trade_regname
,coalesce(t1.amount,0) amount
,coalesce(t2.target,0) ratio_target
from (select period_date, statistical_date, index_name, budget_name, trade_regname, amount
from di_syn.mid_syn_gongyzzh_all_di
where period_date >= ‘2022-01-01’) t1
full join ( select period_date, statistical_date, index_name, budget_name, trade_regname, target
from di_syn.di_syn_gongyzzh_target_df
where period_date >= ‘2022-01-01’
union all
select period_date, statistical_date,
case
when index_name in (‘家电收入’, ‘生态收入’) then
‘场景收入’
when index_name in (‘家电成本’, ‘生态成本’) then
‘场景成本’
when index_name in (‘家电实付’, ‘生态实付’) then
‘实付金额’
end index_name, budget_name, trade_regname, sum(target) target
from di_syn.di_syn_gongyzzh_target_df
where index_name in (‘家电收入’, ‘生态收入’, ‘家电成本’, ‘生态成本’, ‘家电实付’, ‘生态实付’)
and period_date >= ‘2022-01-01’
group by period_date, statistical_date,
case
when index_name in (‘家电收入’, ‘生态收入’) then
‘场景收入’
when index_name in (‘家电成本’, ‘生态成本’) then
‘场景成本’
when index_name in (‘家电实付’, ‘生态实付’) then
‘实付金额’
end, budget_name, trade_regname
) t2
on t1.period_date =t2.period_date
and t1.statistical_date =t2.statistical_date
and t1.index_name =t2.index_name
and t1.budget_name =t2.budget_name
and t1.trade_regname =t2.trade_regname
) t3
where t3.period_date =‘2022-01-03’
and t3.statistical_date =‘2022’
and t3.index_name =‘交互用户’
and t3.budget_name =‘智慧空间’
and t3.trade_regname =‘天津’
如果我执行where前的部分查到这条数最后一个字段是0
如果全部执行，查到的结果是
对于真实数据来说，后者是正确的结果，但定时作业执行的是前者的语句，跑出的结果是错的