case when 属性为空 then 0 else 属性值 end as 属性别名
例子:
@Query(value = "select f.fuel.name as name,case WHEN f.amount is null then 0 else f.amount end as amount,case when f.emissionAmount is null then 0 else f.emissionAmount end as emissionamount," +
"case when f.lvh is null then 0 else f.lvh end as lvh ,case when f.oxygenationEfficiency is null then 0 else f.oxygenationEfficiency end " +
"as oxygenationefficiency,case when f.unitHeatCarbonContent is null then 0 else f.unitHeatCarbonContent end as unitheatcarboncontent,f.fuel.id as fuelid from FuelUsed f" +
" where f.monthReport.id =?1 and f.unitId =?2 and f.status='1' and f.parentId = ?3")
List<FuelUsedProjection> findUnitFuelUsedInfoList(Long reportId,Long unitId,Long parentId);
或者可以使用原生sql,mysql的函数为:IFNULL(属性,0),oralce 的函数 nvl(属性,0)
mysql例子
@Query(value = "select IFNULL(sum(f1.amount),0) as amount,IFNULL(sum(f1.emission_amount),0) as emissionamount,IFNULL(f1.lvh,0) as lvh ,IFNULL(f1.unit_heat_carbon_content,0) AS unitheatcarboncontent," +
" IFNULL(f1.oxygenation_efficiency,0) as oxygenationefficiency,f1.fuel_id as fuelid,f2.name as name from fuel_used f1" +
" LEFT JOIN fuel f2 on f1.fuel_id = f2.id " +
" where f1.month_report_id =?1 " +
" GROUP BY f1.lvh,f1.unit_heat_carbon_content,f1.fuel_id,f1.oxygenation_efficiency order by f1.fuel_id ASC ",nativeQuery = true)
List<FuelUsedProjection> findFuelUsedSummaryInfoList(Long reportId);