问题如图中所述,经验证使用[ select * from( select * from a union all select * from b ) 时会出现这种情况,不知是不是hue的bug ]sql如下:
select translate(date_sub(current_date(),1),'-','') as id,final.*,date_format(date_sub(current_date(),1),'yyyyMM') as dimensions from
(
select n.id as categoryid1,m.* from
(
select case when aaa.categoryname1 is not null then aaa.categoryname1 else bbb.categoryname1 end as categoryname1,
case when aaa.tagvalue is not null then aaa.tagvalue else bbb.tagvalue end as tagvalue,
coalesce(aaa.ordernum,0) as ordernum,
coalesce(aaa.gmv,0) as gmv,
coalesce(bbb.rv,0) as rv
from
(
--2018-01-01至昨天一级分类各活动订单情况
--各一级分类下各活动的订单量与GMV
select aa.categoryname1,bb.tagvalue,count(distinct aa.orderid) as ordernum,sum(aa.amount) as gmv
from hmdbi.activity_orderitem aa
join
(
--除了参加火拼外的带有活动标签的商品
select PromotionId as id,tagvalue from bak_homedo.t_Tag_Promotion
where TagGroupId=30 and Mark>0 and TagValue<>'伙拼' and d=translate(date_sub(current_date(), 2),'-','')
union all
--参加火拼的商品并带有火拼标签
SELECT a.id as id,'伙拼' as tagvalue
FROM bak_homedo.t_product_promotion a
join
bak_homedo.t_Product_Promotion_PinDanCategory b
on b.PromotionId=a.Id and a.Mark>0 and b.Mark>0 and b.Type=2
and a.d=translate(date_sub(current_date(), 2),'-','')
and b.d=translate(date_sub(current_date(), 2),'-','')
) bb
on aa.promotionid = bb.id
where aa.inserttime >= concat(date_format(date_sub(current_date(),1),'yyyy-MM') ,'-01') and aa.inserttime <date_format(current_date(),'yyyy-MM-dd') and aa.isreturn='否'
and aa.promotionid <> 0
group by aa.categoryname1,bb.tagvalue
) aaa
full outer join
(
--各一级分类下各活动的实收
select aa.categoryname1,bb.tagvalue,sum(aa.amount) as rv
from hmdbi.activity_orderitem aa
join
(
select PromotionId as id,tagvalue
from bak_homedo.t_Tag_Promotion
where TagGroupId=30 and Mark>0 and TagValue<>'伙拼'
and d=translate(date_sub(current_date(), 2),'-','')
union all
SELECT a.id as id,'伙拼' as tagvalue
FROM bak_homedo.t_product_promotion a
inner join bak_homedo.t_Product_Promotion_PinDanCategory b on b.PromotionId=a.Id
and a.Mark>0 and b.Mark>0 and b.Type=2
and a.d=translate(date_sub(current_date(), 2),'-','')
and b.d=translate(date_sub(current_date(), 2),'-','')
) bb
on aa.promotionid = bb.id
where aa.effectivetime >= concat(date_format(date_sub(current_date(),1),'yyyy-MM') ,'-01') and aa.effectivetime <date_format(current_date(),'yyyy-MM-dd')
and aa.workflowstatusname in ('交易完成','仓库发货准备','等待客户收货')
and aa.promotionid <> 0
group by aa.categoryname1,bb.tagvalue
) bbb
on aaa.categoryname1=bbb.categoryname1 and aaa.tagvalue=bbb.tagvalue
) m
--得到非oem的categoryid1
left join
(
select id,name from bak_homedo.t_product_category where level=1 and mark>0 and d=date_format(date_sub(current_date(),2),'yyyyMMdd')
) n
on m.categoryname1=n.name
union all
--oem
select case when aaa.categoryid1 is not null then aaa.categoryid1 else bbb.categoryid1 end as categoryid1,
case when aaa.categoryname1 is not null then aaa.categoryname1 else bbb.categoryname1 end as categoryname1,
case when aaa.tagvalue is not null then aaa.tagvalue else bbb.tagvalue end as tagvalue,
coalesce(aaa.ordernum,0) as ordernum,
coalesce(aaa.gmv,0) as gmv,
coalesce(bbb.rv,0) as rv
from
(
--oem的订单量与gmv
select 20000 as categoryid1,'oem' as categoryname1,bb.tagvalue,count(distinct aa.orderid) as ordernum,sum(aa.amount) as gmv
from hmdbi.activity_orderitem aa
join
(
--除了参加火拼外的带有活动标签的商品
select PromotionId as id,tagvalue from bak_homedo.t_Tag_Promotion
where TagGroupId=30 and Mark>0 and TagValue<>'伙拼' and d=translate(date_sub(current_date(), 2),'-','')
union all
--参加火拼的商品并带有火拼标签
SELECT a.id as id,'伙拼' as tagvalue
FROM bak_homedo.t_product_promotion a
join
bak_homedo.t_Product_Promotion_PinDanCategory b
on b.PromotionId=a.Id and a.Mark>0 and b.Mark>0 and b.Type=2
and a.d=translate(date_sub(current_date(), 2),'-','')
and b.d=translate(date_sub(current_date(), 2),'-','')
) bb
on aa.promotionid = bb.id
where aa.inserttime >= concat(date_format(date_sub(current_date(),1),'yyyy-MM') ,'-01') and aa.inserttime <date_format(current_date(),'yyyy-MM-dd') and aa.isreturn='否'
and aa.promotionid <> 0
and aa.brand in ('Ablecan','手把手','康林','英谷','上海允光','上海巴锐','津美韵','迹智','酷显','至配','战匠','博答','矿森','频恒','H3C','跃居')
group by bb.tagvalue
) aaa
full outer join
(
--各一级分类下各活动的实收
select 20000 as categoryid1,'oem' as categoryname1,bb.tagvalue,sum(aa.amount) as rv
from hmdbi.activity_orderitem aa
join
(
select PromotionId as id,tagvalue
from bak_homedo.t_Tag_Promotion
where TagGroupId=30 and Mark>0 and TagValue<>'伙拼'
and d=translate(date_sub(current_date(), 2),'-','')
union all
SELECT a.id as id,'伙拼' as tagvalue
FROM bak_homedo.t_product_promotion a
inner join bak_homedo.t_Product_Promotion_PinDanCategory b on b.PromotionId=a.Id
and a.Mark>0 and b.Mark>0 and b.Type=2
and a.d=translate(date_sub(current_date(), 2),'-','')
and b.d=translate(date_sub(current_date(), 2),'-','')
) bb
on aa.promotionid = bb.id
where aa.effectivetime >= concat(date_format(date_sub(current_date(),1),'yyyy-MM') ,'-01') and aa.effectivetime <date_format(current_date(),'yyyy-MM-dd')
and aa.workflowstatusname in ('交易完成','仓库发货准备','等待客户收货')
and aa.promotionid <> 0
and aa.brand in ('Ablecan','手把手','康林','英谷','上海允光','上海巴锐','津美韵','迹智','酷显','至配','战匠','博答','矿森','频恒','H3C','跃居')
group by bb.tagvalue
) bbb
on aaa.categoryname1=bbb.categoryname1 and aaa.tagvalue=bbb.tagvalue
) final