年龄段:
((year(time1)*10000+month(time1)*100+day(time1))-
(year(time2)*10000+month(time2)*100+day(time2)))
/2000算出来是每5年一个年龄段
精确到天。
((year(time1)*10000+month(time1)*100+day(time1))-
(year(time2)*10000+month(time2)*100+day(time2)))
/2000算出来是每5年一个年龄段
精确到天。
(year(time2)*10000+month(time2)*100+day(time2)))
/10000如果上面看着不习惯,这样算出就是年龄了
所以我觉得还是要用自定义函数。
from 表
group by case when 年龄<30 then -1 when 年龄>59 then 0 else
floor(年龄/5) end
from (
select userid, agegrand=
case
when datediff(year,[生日字段],getdate()) <30 then 0
when datediff(year,[生日字段],getdate()) between 30 and 34 then 1
when datediff(year,[生日字段],getdate()) between 35 and 39 then 2
...
when datediff(year,[生日字段],getdate()) between 50 and 54 then 5
else 6 end
from 表
) t1
group by agegrand
when datediff(year,[生日字段],getdate()) between 35 and 39 then 2
...
when datediff(year,[生日字段],getdate()) between 50 and 54 then 5可以简化为:
when datediff(year,[生日字段],getdate()) between 30 and 54 then (datediff(year,[生日字段],getdate()) -25)/5