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