oracle的是NVL(value,nullvalue)
pgsql的是COALESCE(value,nullvalue)
mysql的是IFNULL(value,nullvalue)
一个表是数据,一个表是项目,关联数据和项目表,查看每个项目有多少人已经有检查结果。
SELECT result2.facility_type_num, result2.facility_type_name, COALESCE(result1.countnum, (0)::bigint) AS "coalesce" FROM (( SELECT project.facility_type_num, project.facility_type_name, 0 AS countnum FROM check_project project) result2 LEFT JOIN ( SELECT cp.facility_type_num, cp.facility_type_name, sum(1) AS countnum FROM (check_project cp JOIN check_result r ON ((((r.facility_type_num)::text = (cp.facility_type_num)::text) AND (to_char(r.lastuptime, 'yyyy-MM-dd'::text) = to_char((CURRENT_DATE)::timestamp with time zone, 'yyyy-MM-dd'::text))))) GROUP BY cp.facility_type_num, cp.facility_type_name ORDER BY cp.facility_type_num) result1 ON (((result1.facility_type_num)::text = (result2.facility_type_num)::text)))