SQL空值判断
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 countnumFROM check_project project) result2LEFT JOIN ( SELECT cp.facility_type_num,cp.facility_type_name,sum(1) AS countnumFROM (check_project cpJOIN 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_nameORDER BY cp.facility_type_num) result1 ON (((result1.facility_type_num)::text = (result2.facility_type_num)::text)))
