用途:
返回传入的多个字段(属性)中第一个非空的值
将空值替换成其他值,返回第一个非空值
表达式:
COALESCE是一个函数, (expression_1, expression_2, ...,expression_n)依次参考各参数表达式,遇到非null值即停止并返回该值。如果所有的表达式都是空值,最终将返回一个空值。使用COALESCE在于大部分包含空值的表达式最终将返回空值。
使用场景:
现需要统计学生上午和下午的测温数据,最终展示效果如下:
学生名 上午温度 下午温度
上午温度为每天的00:00:00 - 12:00:00中,每个学生多次测温的最高的那个温度
下午温度为每天的12:00:00 - 24:00:00中,每个学生多次测温的最高的那个温度
学生可以多次测温,每次都会生成一条记录,数据记录存储的格式
学生iD、温度、测温时间
因为存在这种可能,学生上午测温了,下午没有测温,所以,你需要查找上午每个学生的(最高温度、学生ID), 查找下午每个学生的(最高温度、学生ID)
因为上午和下午某个学生的数据可能为空,所以对于学生ID这一列就不好控制,所以使用coalesce进行操作
coalesce(上午学生ID,下午学生ID)
select COALESCE(morning.name, afternoon.name) as name, COALESCE(morning.class_id, afternoon.class_id) as classId, COALESCE(morning.grade_id, afternoon.grade_id) as gradeId ,
morning.temperature as morningTemperature, afternoon.temperature as afternoonTemperature
from
(
select tbtr.person_id , tbtr.name , tbtr.temperature ,tbtr.class_id ,tbtr.grade_id
from tb_body_temperature_record tbtr , (
select person_id , min(happen_time) as select_happen_time
from tb_body_temperature_record as tr
where tr.happen_time between '2020-09-17 00:00:00' and '2020-09-17 12:00:00' and tr.class_id ='100000000'
group by tr.person_id ) as sd where tbtr.person_id = sd.person_id and tbtr.happen_time = sd.select_happen_time order by tbtr.person_id
) as morning
full join
(
select tbtr.person_id , tbtr.name , tbtr.temperature ,tbtr.class_id ,tbtr.grade_id
from tb_body_temperature_record tbtr , (
select person_id , min(happen_time) as select_happen_time
from tb_body_temperature_record as tr
where tr.happen_time between '2020-09-17 12:00:00' and '2020-09-17 24:00:00' and tr.class_id ='100000000'
group by tr.person_id
) as sd
where tbtr.person_id = sd.person_id and tbtr.happen_time = sd.select_happen_time
order by tbtr.person_id ) as afternoon
on morning.person_id = afternoon.person_id