一、应用场景
在遇到多张表查询时,很可能查一个关联数值时,并没有这条关联记录,所以查询到的结果是null,通常需要把这个结果处理成0或者其他。
这时候就用isNULL(字段,0)
如:
二、sql server可用
select
car_repair.supplier_name ,
isNULL(sum(car_repair.tocal_maintain_money),0)+isNULL(sum(car_repair_work_hours_total.sum_work_hour_money),0) ,
isNULL(sum(car_repair_info_total.sum_wz_total),0),
isNULL(sum(car_repair_work_hours_total.sum_work_hour_money),0) ,
cast(year(car_repair.repairs_time) as varchar)+'/'+cast(month
(car_repair.repairs_time) as varchar)
from car_repair
left join (select repair_id,sum(wz_total) as sum_wz_total from car_repair_info group by repair_id) as car_repair_info_total
on car_repair.id=car_repair_info_total.repair_id
left join (select repair_id,sum(work_hour_money) as sum_work_hour_money from car_repair_work_hours group by repair_id) as car_repair_work_hours_total
on car_repair.id=car_repair_work_hours_total.repair_id
group by
car_repair.supplier_name,
cast(year(car_repair.repairs_time) as varchar)
+'/'+cast(month(car_repair.repairs_time) as varchar)
三、MYSQL可用
select cource.c_id,cource.c_name,cource.c_num,ifnull(student.count_c_id,'lattice') from cource
left join
(select c_id,count(s_id) as count_c_id from cource_student group by c_id) as student
on cource.c_id=student.c_id;