测试用了几种mysql 查询结果null转0的写法,均不通过,查询的结果都是null,然后找了百度叔叔,终于破解了,特此记录:
-- 原来的写法 SELECT count(*), COALESCE (count(*), 0), IFNULL(count(*),0), CASE count(*) WHEN count(*) is NULL THEN 0 ELSE count(*) END FROM stock_palautus AS sp LEFT JOIN purchase_suppliers AS ps ON sp.SP_SUPPLIER_ID = ps.PS_ID LEFT JOIN stock_palautus_detail spd ON spd.SPD_SP_ID = sp.SP_ID LEFT JOIN materials m ON m.MATERIALS_ID = spd.SPD_MATERIALS_ID WHERE SP_DEL =0 AND SP_STATUS IN (1, 5) AND SPD_STATUS IN (0, 2) GROUP BY SP_ID -- 修改后的写法,用了子查询 SELECT count(*) FROM( SELECT SP_ID FROM stock_palautus AS sp LEFT JOIN purchase_suppliers AS ps ON sp.SP_SUPPLIER_ID = ps.PS_ID LEFT JOIN stock_palautus_detail spd ON spd.SPD_SP_ID = sp.SP_ID LEFT JOIN materials m ON m.MATERIALS_ID = spd.SPD_MATERIALS_ID WHERE SP_DEL =0 AND SP_STATUS IN (1, 5) AND SPD_STATUS IN (0, 2) GROUP BY SP_ID ) as bb