当想要统计的两个指标不在一张表中时，需要做关联。但很多情况下，也没有办法保证其中一张表的维度是全的，用left join或right join可能会导致数据丢失。所以借助full join处理。
1）如，将下面的数据处理成表格中的效果（维度就是name）。
目标效果：
| name | number | show_number |
|---|---|---|
| A | 300 | 0.80 |
| B | 100 | NULL |
| C | 150 | 0.20 |
| D | NULL | 0.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.name=t2.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,'red'union 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,'green'union all select 'C',0.2,'green') as t2 on t1.name=t2.name and t1.type=t2.type
) as r
结果：
