SQL CASE

SELECT L.id, L.name, L.zero_three_num, L.four_six_num, seven_nine_num, ten_or_more_num,
(L.zero_three_num+L.four_six_num+seven_nine_num+ten_or_more_num) total
FROM (SELECT r.id, r.name,
CASE ISNULL(s.zero_three_num,'')--处理null值,因为null与任务数相加都是null,这样结果集无法排序.
WHEN '' THEN 0 ELSE s.zero_three_num --如果不是null值则,使用原来的值.
END zero_three_num,--0至3岁人的数量
CASE ISNULL(s.four_six_num,'')
WHEN '' THEN 0 ELSE s.four_six_num
END four_six_num,--4至6岁
CASE ISNULL(s.seven_nine_num,'')
WHEN '' THEN 0 ELSE s.seven_nine_num
END seven_nine_num,-- 7至9岁
CASE ISNULL(s.ten_or_more_num,'')
WHEN '' THEN 0 ELSE s.ten_or_more_num
END ten_or_more_num --10岁以上
FROM gos_region r
LEFT OUTER JOIN
(SELECT t.${region_id_macro},
SUM(
CASE
WHEN t.child_age IN (0,1,2,3) THEN 1 ELSE 0
END) zero_three_num,--0至3岁人的数量
SUM(
CASE
WHEN t.child_age IN (4,5,6) THEN 1 ELSE 0
END) four_six_num, --4至6岁
SUM(
CASE
WHEN t.child_age IN (7,8,9) THEN 1 ELSE 0
END) seven_nine_num, -- 7至9岁
SUM(
CASE
WHEN t.child_age >=10 THEN 1 ELSE 0
END) ten_or_more_num --10岁以上

FROM "Xxx_INFO" t
WHERE to_char(t.publish_date, 'yyyy-MM-dd') >= ?
and to_char(t.publish_date, 'yyyy-MM-dd') <= ? AND t.publish_state='1'
GROUP BY t.${region_id_macro}
) s
ON r.id = s.${region_id_macro}
WHERE ${g2s_region_where} AND r.use2_flag=1
) L
ORDER BY total DESC;