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;