我的数据如下,children表id birthday studydate
1 2008-1-1 2009-1-1
2 2006-1-1 2009-1-1
3 2007-1-1 2009-1-1
4 2008-4-1 2009-1-1
...
...
...实现的效果是,birthday 生日,studydate ,上学日期
(studydate -birthday ) /365 如果是 >0 并且<= 1 就是 “一年后读书的”
如果是 >1 并且小于 <=2 那么就是 “二年后读书的”,最后得到每年的人数。根据以上表生成的信息应该是:年 度 人 数
一年 2
二年 1
三年 1
请问怎么实现啊。。
1 2008-1-1 2009-1-1
2 2006-1-1 2009-1-1
3 2007-1-1 2009-1-1
4 2008-4-1 2009-1-1
...
...
...实现的效果是,birthday 生日,studydate ,上学日期
(studydate -birthday ) /365 如果是 >0 并且<= 1 就是 “一年后读书的”
如果是 >1 并且小于 <=2 那么就是 “二年后读书的”,最后得到每年的人数。根据以上表生成的信息应该是:年 度 人 数
一年 2
二年 1
三年 1
请问怎么实现啊。。
when (studydate -birthday )/365<=2 then '二年'
else '二年以上' end 年度,count(1) 人数
from children
where studydate -birthday>0
group by (case when (studydate -birthday )/365<=1 then 1
when (studydate -birthday )/365<=2 then 2
else 3 end)
如果不要二年以上而是三年,就加个条件
and (studydate -birthday )/365<=3
然后将'二年以上'改成'三年'
补充下,我在1楼,不是2楼..
不能误导人哦·
count返回记录数,还是返回某个列值数
是你没弄清楚吧
select case
when (inoc_date - chil_birthday) / 365 >= 0 and
(inoc_date - chil_birthday) / 365 < 1 then
'0-岁组'
when (inoc_date - chil_birthday) / 365 >= 1 and
(inoc_date - chil_birthday) / 365 < 2 then
'1-岁组'
when (inoc_date - chil_birthday) / 365 >= 2 and
(inoc_date - chil_birthday) / 365 < 3 then
'2-岁组'
when (inoc_date - chil_birthday) / 365 >= 3 and
(inoc_date - chil_birthday) / 365 < 4 then
'3-岁组'
when (inoc_date - chil_birthday) / 365 >= 4 and
(inoc_date - chil_birthday) / 365 < 5 then
'4-岁组'
when (inoc_date - chil_birthday) / 365 >= 5 and
(inoc_date - chil_birthday) / 365 < 6 then
'5-岁组'
when (inoc_date - chil_birthday) / 365 >= 6 and
(inoc_date - chil_birthday) / 365 < 7 then
'6-岁组'
when (inoc_date - chil_birthday) / 365 >= 7 and
(inoc_date - chil_birthday) / 365 < 8 then
'7-岁组'
when (inoc_date - chil_birthday) / 365 >= 8 and
(inoc_date - chil_birthday) / 365 < 9 then
'8-岁组'
when (inoc_date - chil_birthday) / 365 >= 9 and
(inoc_date - chil_birthday) / 365 < 10 then
'9-岁组'
when (inoc_date - chil_birthday) / 365 >= 10 and
(inoc_date - chil_birthday) / 365 < 11 then
'10-岁组'
when (inoc_date - chil_birthday) / 365 >= 11 and
(inoc_date - chil_birthday) / 365 <= 15 then
'15-岁组'
when (inoc_date - chil_birthday) / 365 > 15 and
(inoc_date - chil_birthday) / 365 <= 20 then
'20-岁组'
when (inoc_date - chil_birthday) / 365 > 20 and
(inoc_date - chil_birthday) / 365 <= 25 then
'25-岁组'
when (inoc_date - chil_birthday) / 365 > 25 and
(inoc_date - chil_birthday) / 365 <= 30 then
'30-岁组'
when (inoc_date - chil_birthday) / 365 > 30 and
(inoc_date - chil_birthday) / 365 <= 35 then
'35-岁组'
when (inoc_date - chil_birthday) / 365 > 35 and
(inoc_date - chil_birthday) / 365 <= 40 then
'40-岁组'
when (inoc_date - chil_birthday) / 365 > 40 and
(inoc_date - chil_birthday) / 365 <= 45 then
'45-岁组'
when (inoc_date - chil_birthday) / 365 > 45 and
(inoc_date - chil_birthday) / 365 <= 50 then
'50-岁组'
when (inoc_date - chil_birthday) / 365 > 50 and
(inoc_date - chil_birthday) / 365 <= 55 then
'55-岁组'
when (inoc_date - chil_birthday) / 365 > 55 and
(inoc_date - chil_birthday) / 365 <= 60 then
'60-岁组'
when (inoc_date - chil_birthday) / 365 > 60 and
(inoc_date - chil_birthday) / 365 <= 65 then
'65-岁组'
when (inoc_date - chil_birthday) / 365 > 65 and
(inoc_date - chil_birthday) / 365 <= 70 then
'70-岁组'
when (inoc_date - chil_birthday) / 365 > 70 and
(inoc_date - chil_birthday) / 365 <= 75 then
'75-岁组'
when (inoc_date - chil_birthday) / 365 > 75 and
(inoc_date - chil_birthday) / 365 <= 80 then
'80-岁组'
when (inoc_date - chil_birthday) / 365 > 80 and
(inoc_date - chil_birthday) / 365 <= 85 then
'85-岁组'
when (inoc_date - chil_birthday) / 365 > 85 then
'85-及以上'
else
'其他'
end ageGroup,
count(1) 人数
from child, Inoculation, department
where inoc_chil_id = chil_id
and chil_curdepartment = depa_id
and depa_code like '440106%'
and inoc_date - chil_birthday > 0 group by (case
when (inoc_date - chil_birthday) / 365 >= 0 and
(inoc_date - chil_birthday) / 365 < 1 then
0
when (inoc_date - chil_birthday) / 365 >= 1 and
(inoc_date - chil_birthday) / 365 < 2 then
1
when (inoc_date - chil_birthday) / 365 >= 2 and
(inoc_date - chil_birthday) / 365 < 3 then
2
when (inoc_date - chil_birthday) / 365 >= 3 and
(inoc_date - chil_birthday) / 365 < 4 then
3
when (inoc_date - chil_birthday) / 365 >= 4 and
(inoc_date - chil_birthday) / 365 < 5 then
4
when (inoc_date - chil_birthday) / 365 >= 5 and
(inoc_date - chil_birthday) / 365 < 6 then
5
when (inoc_date - chil_birthday) / 365 >= 6 and
(inoc_date - chil_birthday) / 365 < 7 then
6
when (inoc_date - chil_birthday) / 365 >= 7 and
(inoc_date - chil_birthday) / 365 < 8 then
7
when (inoc_date - chil_birthday) / 365 >= 8 and
(inoc_date - chil_birthday) / 365 < 9 then
8
when (inoc_date - chil_birthday) / 365 >= 9 and
(inoc_date - chil_birthday) / 365 < 10 then
9
when (inoc_date - chil_birthday) / 365 >= 10 and
(inoc_date - chil_birthday) / 365 < 11 then
10
when (inoc_date - chil_birthday) / 365 >= 11 and
(inoc_date - chil_birthday) / 365 <= 15 then
15
when (inoc_date - chil_birthday) / 365 > 15 and
(inoc_date - chil_birthday) / 365 <= 20 then
20
when (inoc_date - chil_birthday) / 365 > 20 and
(inoc_date - chil_birthday) / 365 <= 25 then
25
when (inoc_date - chil_birthday) / 365 > 25 and
(inoc_date - chil_birthday) / 365 <= 30 then
30
when (inoc_date - chil_birthday) / 365 > 30 and
(inoc_date - chil_birthday) / 365 <= 35 then
35
when (inoc_date - chil_birthday) / 365 > 35 and
(inoc_date - chil_birthday) / 365 <= 40 then
40
when (inoc_date - chil_birthday) / 365 > 40 and
(inoc_date - chil_birthday) / 365 <= 45 then
45
when (inoc_date - chil_birthday) / 365 > 45 and
(inoc_date - chil_birthday) / 365 <= 50 then
50
when (inoc_date - chil_birthday) / 365 > 50 and
(inoc_date - chil_birthday) / 365 <= 55 then
55
when (inoc_date - chil_birthday) / 365 > 55 and
(inoc_date - chil_birthday) / 365 <= 60 then
60
when (inoc_date - chil_birthday) / 365 > 60 and
(inoc_date - chil_birthday) / 365 <= 65 then
65
when (inoc_date - chil_birthday) / 365 > 65 and
(inoc_date - chil_birthday) / 365 <= 70 then
70
when (inoc_date - chil_birthday) / 365 > 70 and
(inoc_date - chil_birthday) / 365 <= 75 then
75
when (inoc_date - chil_birthday) / 365 > 75 and
(inoc_date - chil_birthday) / 365 <= 80 then
80
when (inoc_date - chil_birthday) / 365 > 80 and
(inoc_date - chil_birthday) / 365 <= 85 then
85
when (inoc_date - chil_birthday) / 365 > 85 then
-2
else
-1
end)
(case
when (inoc_date - chil_birthday) / 365 >= 0 and
(inoc_date - chil_birthday) / 365 < 1 then
'0-岁组'
when (inoc_date - chil_birthday) / 365 >= 1 and
(inoc_date - chil_birthday) / 365 < 2 then
'1-岁组'
when (inoc_date - chil_birthday) / 365 >= 2 and
(inoc_date - chil_birthday) / 365 < 3 then
'2-岁组'
when (inoc_date - chil_birthday) / 365 >= 3 and
(inoc_date - chil_birthday) / 365 < 4 then
'3-岁组'
when (inoc_date - chil_birthday) / 365 >= 4 and
(inoc_date - chil_birthday) / 365 < 5 then
'4-岁组'
when (inoc_date - chil_birthday) / 365 >= 5 and
(inoc_date - chil_birthday) / 365 < 6 then
'5-岁组'
when (inoc_date - chil_birthday) / 365 >= 6 and
(inoc_date - chil_birthday) / 365 < 7 then
'6-岁组'
when (inoc_date - chil_birthday) / 365 >= 7 and
(inoc_date - chil_birthday) / 365 < 8 then
'7-岁组'
when (inoc_date - chil_birthday) / 365 >= 8 and
(inoc_date - chil_birthday) / 365 < 9 then
'8-岁组'
when (inoc_date - chil_birthday) / 365 >= 9 and
(inoc_date - chil_birthday) / 365 < 10 then
'9-岁组'
when (inoc_date - chil_birthday) / 365 >= 10 and
(inoc_date - chil_birthday) / 365 < 11 then
'10-岁组'
when (inoc_date - chil_birthday) / 365 >= 11 and
(inoc_date - chil_birthday) / 365 <= 15 then
'15-岁组'
when (inoc_date - chil_birthday) / 365 > 15 and
(inoc_date - chil_birthday) / 365 <= 20 then
'20-岁组'
when (inoc_date - chil_birthday) / 365 > 20 and
(inoc_date - chil_birthday) / 365 <= 25 then
'25-岁组'
when (inoc_date - chil_birthday) / 365 > 25 and
(inoc_date - chil_birthday) / 365 <= 30 then
'30-岁组'
when (inoc_date - chil_birthday) / 365 > 30 and
(inoc_date - chil_birthday) / 365 <= 35 then
'35-岁组'
when (inoc_date - chil_birthday) / 365 > 35 and
(inoc_date - chil_birthday) / 365 <= 40 then
'40-岁组'
when (inoc_date - chil_birthday) / 365 > 40 and
(inoc_date - chil_birthday) / 365 <= 45 then
'45-岁组'
when (inoc_date - chil_birthday) / 365 > 45 and
(inoc_date - chil_birthday) / 365 <= 50 then
'50-岁组'
when (inoc_date - chil_birthday) / 365 > 50 and
(inoc_date - chil_birthday) / 365 <= 55 then
'55-岁组'
when (inoc_date - chil_birthday) / 365 > 55 and
(inoc_date - chil_birthday) / 365 <= 60 then
'60-岁组'
when (inoc_date - chil_birthday) / 365 > 60 and
(inoc_date - chil_birthday) / 365 <= 65 then
'65-岁组'
when (inoc_date - chil_birthday) / 365 > 65 and
(inoc_date - chil_birthday) / 365 <= 70 then
'70-岁组'
when (inoc_date - chil_birthday) / 365 > 70 and
(inoc_date - chil_birthday) / 365 <= 75 then
'75-岁组'
when (inoc_date - chil_birthday) / 365 > 75 and
(inoc_date - chil_birthday) / 365 <= 80 then
'80-岁组'
when (inoc_date - chil_birthday) / 365 > 80 and
(inoc_date - chil_birthday) / 365 <= 85 then
'85-岁组'
when (inoc_date - chil_birthday) / 365 > 85 then
'85-及以上'
else
'其他'
end);试试