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;