HQL-字段为NULL时设置为默认值 0 的写法

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);