oracle用sql语句实现年龄分段统计，case when then用法(用于分类统计)

sql 对一张表进行按照不同条件进行多次统计 在这里使用 case when then(多用于分类统计)

SELECT CASE WHEN age < 30 THEN '30岁以下' WHEN age BETWEEN 30 AND 35 THEN '30-35岁' WHEN age BETWEEN 36 AND 40 THEN '36-40岁' WHEN age > 40 THEN '40岁以上' END AS NAME, count(*) AS count FROM ( SELECT ( SELECT to_number (to_char(sysdate, 'yyyy')) FROM DUAL ) - substr(csny, 0, 4) AS age FROM t_xwr_lz_grxx b, t_xwr_sys_user c, t_xwr_sys_dept d WHERE b.xm = c.user_id AND c.dept_id = d.dept_id AND ( SELECT to_number (to_char(sysdate, 'yyyy')) FROM DUAL ) - substr(csny, 0, 4) IS NOT NULL ) GROUP BY CASE WHEN age < 30 THEN '30岁以下' WHEN age BETWEEN 30 AND 35 THEN '30-35岁' WHEN age BETWEEN 36 AND 40 THEN '36-40岁' WHEN age > 40 THEN '40岁以上' END;

解释以下：

由于数据库里没有年龄字段，这里只能由 获取当年年份 - 出生年 所有有了这个写法： 当年年份 - 出生年 (select to_number(to_char(sysdate, ‘yyyy’ )) from dual) - substr(csny,0,4) as age

结果如下所示