非空查找函数: COALESCE
语法: COALESCE(T v1, T v2,…)
说明: 返回参数中的第一个非空值;如果所有值都为NULL,那么返回NULL
举例:
select
COALESCE(t1.area_code
,t2.area_code
,t3.area_code
,t4.area_code
,t5.area_code
)
,COALESCE(t1.area_name
,t2.area_name
,t3.area_name
,t4.area_name
,t5.area_name
)
,COALESCE(t1.day_time,t2.day_time,t3.day_time,t4.day_time,t5.day_time)
from
ads_smart_community.ads_person_sum t1
full outer join
ads_smart_community.ads_vehicle_sum t2
on t1.area_code = t2.area_code and t1.day_time = t2.day_time
full outer join
ads_smart_community.ads_judge_sum t3
on t1.area_code = t3.area_code and t1.day_time = t3.day_time
full outer join
ads_smart_community.ads_device_sum t4
on t1.area_code = t4.area_code and t1.day_time = t4.day_time
full outer join
ads_smart_community.ads_technical_defense_alarm t5
on t1.area_code = t5.area_code and t1.day_time = t5.day_time
;