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
结果如下所示