公司邮箱怎么申请的,哈尔滨seo优化公司多少钱,百度小程序api,网站目录链接怎么做当想要统计的两个指标不在一张表中时#xff0c;需要做关联。但很多情况下#xff0c;也没有办法保证其中一张表的维度是全的#xff0c;用left join或right join可能会导致数据丢失。所以借助full join处理。
1#xff09;如#xff0c;将下面的数据处理成表格中的效果需要做关联。但很多情况下也没有办法保证其中一张表的维度是全的用left join或right join可能会导致数据丢失。所以借助full join处理。
1如将下面的数据处理成表格中的效果维度就是name。 目标效果
namenumbershow_numberA3000.80B100NULLC1500.20DNULL0.66
select if(r.name is null ,r.name2,r.name) as name, -- 保留有值的namer.number,r.show_number
from
( -- 数据合并select t1.name,t1.number,t2.name as name2,t2.show_numberfrom (select A as name,300 as numberunion all select B ,100union all select C ,150 ) as t1full join (select A as name,0.8 as show_numberunion all select D,0.66union all select C,0.2) as t2 on t1.namet2.name
) as r结果 2如果再增加一个维度type的话
select if(r.name is null ,r.name2,r.name) as name, -- 保留有值的nameif(r.type is null ,r.type2,r.type) as type,r.number,r.show_number-- r.type2
from
( -- 数据合并select t1.name,t1.number,t1.type,t2.name as name2,t2.show_number,t2.type as type2from (select A as name,300 as number,red as typeunion all select B ,100,redunion all select C ,150 ,red) as t1full join (select A as name,0.8 as show_number,red as typeunion all select D,0.66,greenunion all select C,0.2,green) as t2 on t1.namet2.name and t1.typet2.type
) as r结果