union all 丢失部分数据

Wed, 04 Nov 2020 02:22:03 -0800 

开发者好：
               目前有此场景：算不同部门的总收入和所有部门的总收入，打算把两部分SQL union all  ,但是实际情况发现 union 
all的时候会丢一部分数据，要么是各个部门的数据少了，要么是所有部门的总收入少了
               如果把union  all 的两段SQL 分别独立出来，插入同一张表，那么数据就是正常的，不知道是否是bug还是使用方法不对




原sql :


insert into dws_XXXX


select 
0 as id
,cast (DATE_FORMAT(LOCALTIMESTAMP, 'yyyyMMddHH') as bigint) as ftime
,case 
when dept_name like '%XX%' then 'X1'
when dept_name = 'xXX' then 'X2'
else 'X3' end as paytype
,count(orderid) as paynum_h 
,round(sum(amt)) as paymoney_h 
from dwd_XXX
where write_time >=DATE_FORMAT(LOCALTIMESTAMP, 'yyyy-MM-dd')
group by
DATE_FORMAT(LOCALTIMESTAMP, 'yyyyMMddHH'), 
case 
when dept_name like '%XX%' then 'X1'
when dept_name = 'xXX' then 'X2'
else 'X3' end ;




union all




select 0 as id
,cast (DATE_FORMAT(LOCALTIMESTAMP, 'yyyyMMddHH') as int) as ftime
,'all' as paytype
,count(orderid) as paynum_h  
,round(sum(amt)) as paymoney_h  
from dwd_XXX
where write_time >=DATE_FORMAT(LOCALTIMESTAMP, 'yyyy-MM-dd')
group by DATE_FORMAT(LOCALTIMESTAMP, 'yyyyMMddHH') ;

回复