SQL语句分年龄段以5岁为递增

SQL语句分年龄段以5岁为递增

 数据：

 

if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([姓名] varchar(1),[部门] varchar(4),[学历] varchar(4),[出生年月] datetime)
insert [tb]
select 'A','后勤','高中','1986-1-1' union all
select 'B','后勤','初中','1984-3-7' union all
select 'C','管理','本科','1987-2-1' union all
select 'D','操作','专科','1976-2-1' union all
select 'E','操作','专科','1943-2-1' 


--------------开始查询--------------------------
select *,dbo.AgeLevel([出生年月]) as 年龄段 from tb

 

 

函数：

drop function AgeLevel 
go 
--获取年龄段 
create function AgeLevel(@birthday datetime) 
returns varchar(10) 
as 
begin 
declare  @AgeLevel varchar(10) 
declare @Age int
Set @Age=datediff(year,@birthday,getdate())-1
select @AgeLevel=case(@Age/10) 
when 1 then '20以下' 
when 2 then (case(@Age-20)/6 when 0 then '20~25' when 1 then '26~30' end)
when 3 then (case(@Age-30)/6 when 0 then '30~35' when 1 then '36~40' end)
when 4 then (case(@Age-40)/6 when 0 then '40~45' when 1 then '46~50' end) 
else '50以上' 
end  
return @AgeLevel 
end 
go