SQL：一行中存在任一指标就显示出来

当想要统计的两个指标不在一张表中时，需要做关联。但很多情况下，也没有办法保证其中一张表的维度是全的，用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