--参考: select case trunc(months_between(sysdate,hiredate)/12) when between 0 and 9 then '0-9' when between 10 and 19 then '10-19' when between 20 and 29 then '20-29' when between 30 and 39 then '30-39' when between 40 and 49 then '40-49' when between 50 and 59 then '50-59' when between 60 and 69 then '60-69' when between 70 and 79 then '70-79' when between 80 and 89 then '80-89' else '90-99' end age_range, sex, count(*) cn from tb_name group by age_range,sex
好像LZ要统计人数吧,所以用一个case应该不够吧
问题描述的不清楚,随便写一个 SQL> WITH t AS ( 2 SELECT TO_DATE('19990101','yyyymmdd') birth,'男' sex FROM DUAL UNION ALL 3 SELECT TO_DATE('20050101','yyyymmdd') ,'男' FROM DUAL UNION ALL 4 SELECT TO_DATE('19540101','yyyymmdd') ,'男' FROM DUAL UNION ALL 5 SELECT TO_DATE('19640101','yyyymmdd') ,'男' FROM DUAL UNION ALL 6 SELECT TO_DATE('19740101','yyyymmdd') ,'女' FROM DUAL UNION ALL 7 SELECT TO_DATE('19840101','yyyymmdd') ,'女' FROM DUAL UNION ALL 8 SELECT TO_DATE('19540101','yyyymmdd') ,'男' FROM DUAL UNION ALL 9 SELECT TO_DATE('19640101','yyyymmdd') ,'男' FROM DUAL UNION ALL 10 SELECT TO_DATE('19740101','yyyymmdd') ,'女' FROM DUAL UNION ALL 11 SELECT TO_DATE('19840101','yyyymmdd') ,'女' FROM DUAL UNION ALL 12 SELECT TO_DATE('19940101','yyyymmdd') ,'男' FROM DUAL 13 ) 14 SELECT CASE WHEN year_range = '0' THEN '0-9' 15 WHEN year_range = '1' THEN '10-19' 16 WHEN year_range = '2' THEN '20-29' 17 WHEN year_range = '3' THEN '30-39' 18 WHEN year_range = '4' THEN '40-49' 19 WHEN year_range = '5' THEN '50-59' 20 WHEN year_range = '6' THEN '60-69' 21 WHEN year_range = '7' THEN '70-79' 22 WHEN year_range = '8' THEN '80-89' 23 ELSE '90-99' END, 24 sex, 25 num 26 FROM ( 27 SELECT to_char(FLOOR((SYSDATE - birth) / 3650)) year_range,sex,COUNT(*) num FROM t GROUP BY FLO OR((SYSDATE - birth) / 3650),sex) M 28 ORDER BY sex,year_range 29 ;CASEWHENYE SEX NUM ---------- ------ ---------- 20-29 女 2 30-39 女 2 0-9 男 1 10-19 男 2 40-49 男 2 50-59 男 26行が選択されました。
--参考:
select case trunc(months_between(sysdate,hiredate)/12)
when between 0 and 9 then '0-9'
when between 10 and 19 then '10-19'
when between 20 and 29 then '20-29'
when between 30 and 39 then '30-39'
when between 40 and 49 then '40-49'
when between 50 and 59 then '50-59'
when between 60 and 69 then '60-69'
when between 70 and 79 then '70-79'
when between 80 and 89 then '80-89'
else '90-99'
end age_range,
sex,
count(*) cn
from tb_name
group by age_range,sex
好像LZ要统计人数吧,所以用一个case应该不够吧
SQL> WITH t AS (
2 SELECT TO_DATE('19990101','yyyymmdd') birth,'男' sex FROM DUAL UNION ALL
3 SELECT TO_DATE('20050101','yyyymmdd') ,'男' FROM DUAL UNION ALL
4 SELECT TO_DATE('19540101','yyyymmdd') ,'男' FROM DUAL UNION ALL
5 SELECT TO_DATE('19640101','yyyymmdd') ,'男' FROM DUAL UNION ALL
6 SELECT TO_DATE('19740101','yyyymmdd') ,'女' FROM DUAL UNION ALL
7 SELECT TO_DATE('19840101','yyyymmdd') ,'女' FROM DUAL UNION ALL
8 SELECT TO_DATE('19540101','yyyymmdd') ,'男' FROM DUAL UNION ALL
9 SELECT TO_DATE('19640101','yyyymmdd') ,'男' FROM DUAL UNION ALL
10 SELECT TO_DATE('19740101','yyyymmdd') ,'女' FROM DUAL UNION ALL
11 SELECT TO_DATE('19840101','yyyymmdd') ,'女' FROM DUAL UNION ALL
12 SELECT TO_DATE('19940101','yyyymmdd') ,'男' FROM DUAL
13 )
14 SELECT CASE WHEN year_range = '0' THEN '0-9'
15 WHEN year_range = '1' THEN '10-19'
16 WHEN year_range = '2' THEN '20-29'
17 WHEN year_range = '3' THEN '30-39'
18 WHEN year_range = '4' THEN '40-49'
19 WHEN year_range = '5' THEN '50-59'
20 WHEN year_range = '6' THEN '60-69'
21 WHEN year_range = '7' THEN '70-79'
22 WHEN year_range = '8' THEN '80-89'
23 ELSE '90-99' END,
24 sex,
25 num
26 FROM (
27 SELECT to_char(FLOOR((SYSDATE - birth) / 3650)) year_range,sex,COUNT(*) num FROM t GROUP BY FLO
OR((SYSDATE - birth) / 3650),sex) M
28 ORDER BY sex,year_range
29 ;CASEWHENYE SEX NUM
---------- ------ ----------
20-29 女 2
30-39 女 2
0-9 男 1
10-19 男 2
40-49 男 2
50-59 男 26行が選択されました。