SQL语句分年龄段以5岁为递增
2011-10-19 23:50 starlet 阅读(614) 评论(0) 编辑 收藏 举报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
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
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