select '0-20' as 年龄段,count(1) as 人数 from Employee where datediff(y,Birth,getdate()) between 0 and 20 union all select '20-30' ,count(1) from Employee where datediff(y,Birth,getdate()) between 21 and 30 union all select '30-40' ,count(1) from Employee where datediff(y,Birth,getdate()) between 31 and 40
CREATE TABLE Employee (ID INT ,BIRTH DATETIME) INSERT Employee SELECT 1,'2001-1-2' UNION ALL SELECT 2,'2002-1-2' UNION ALL SELECT 3,'2003-1-2' UNION ALL SELECT 4,'2004-1-2' UNION ALL SELECT 5,'1987-1-2' UNION ALL SELECT 6,'1986-1-2' UNION ALL SELECT 7,'1966-1-2' UNION ALL SELECT 8,'1965-1-2' SELECT 年龄段='0-20',COUNT(*)AS 人数 FROM Employee WHERE DATEDIFF(YY,BIRTH,GETDATE())>0 AND DATEDIFF(YY,BIRTH,GETDATE())<20UNION ALLSELECT 年龄段='20-30',COUNT(*)AS 人数 FROM Employee WHERE DATEDIFF(YY,BIRTH,GETDATE())>=20 AND DATEDIFF(YY,BIRTH,GETDATE())<30UNION ALLSELECT 年龄段='30-40',COUNT(*)AS 人数 FROM Employee WHERE DATEDIFF(YY,BIRTH,GETDATE())>=30 AND DATEDIFF(YY,BIRTH,GETDATE())<40UNION ALLSELECT 年龄段='大于40',COUNT(*)AS 人数 FROM Employee WHERE DATEDIFF(YY,BIRTH,GETDATE())>=40年龄段 人数 ------ ----------- 0-20 4 20-30 2 30-40 0 大于40 2(所影响的行数为 4 行)
select count(Birth) num,Birth as age from table where dateiff(yy,Birth,GETDATE()) between o and 20 group by Birth union all select count(Birth) num,Birth as age from table where dateiff(yy,Birth,GETDATE()) between 20 and 30 group by Birth union all select count(Birth) num,Birth as age from table where dateiff(yy,Birth,GETDATE()) between 30 and 40 group by Birth
CREATE PROCEDURE TJ_nianl AS declare @icount int, @icount4 int,@JU varchar(20),@year int
BEGIN select @icount =count(*) from Tb_yhxx where 1=1 set @year=year(getdate()) select @year - convert(int,substring(convert(varchar, isnull(a.chusrq,0),21),1,4)) as nianl into #ls_1 from Tb_yhxx a select 6 as xh, convert(varchar(20),'20岁及以下 ') as nld ,count(*) as rs into #ls_2 from #ls_1 where nianl<=20 insert into #ls_2(xh,nld, rs) select 5 as xh, '20岁到30岁' as nld ,count(*) as rs from #ls_1 where nianl<=30 and nianl>20 insert into #ls_2(xh,nld, rs) select 4 as xh,'30岁到40岁' as nld ,count(*) as rs from #ls_1 where nianl<=40 and nianl>30 insert into #ls_2(xh,nld, rs) select 3 as xh,'40岁到50岁' as nld ,count(*) as rs from #ls_1 where nianl<=50 and nianl>40 insert into #ls_2(xh,nld, rs) select 2 as xh,'50岁到60岁' as nld ,count(*) as rs from #ls_1 where nianl<=60 and nianl>50 insert into #ls_2(xh,nld, rs) select 1 as xh,'60岁以上' as nld ,count(*) as rs from #ls_1 where nianl<=100 and nianl>60 select @icount4 =sum(rs) from #ls_2 if(@icount -@icount4 >0) BEGIN insert #ls_2(xh, nld, rs ) values( 7,'年龄不详',@icount -@icount4 ) end select * from #ls_2 order by xh drop table #ls_1 drop table #ls_2 end
提供一個參考CREATE TABLE #Employee (ID INT ,BIRTH DATETIME) INSERT #Employee SELECT 1,'2001-1-2' UNION ALL SELECT 2,'2002-1-2' UNION ALL SELECT 3,'2003-1-2' UNION ALL SELECT 4,'2004-1-2' UNION ALL SELECT 5,'1987-1-2' UNION ALL SELECT 6,'1986-1-2' UNION ALL SELECT 7,'1976-1-2' UNION ALL SELECT 8,'1975-1-2' select age,count(1)as qty from (select (case when datediff(yy,BIRTH,getdate()) between 0 and 20 then '0-20' when datediff(yy,BIRTH,getdate()) between 20 and 30 then '20-30' when datediff(yy,BIRTH,getdate()) between 30 and 40 then '30-40' end)as age from #Employee)A group by age/* age qty ----- ----------- 0-20 4 20-30 2 30-40 2(3 個資料列受到影響)*/drop table #Employee
union all
select '20-30' ,count(1) from Employee where datediff(y,Birth,getdate()) between 21 and 30
union all
select '30-40' ,count(1) from Employee where datediff(y,Birth,getdate()) between 31 and 40
INSERT Employee
SELECT 1,'2001-1-2' UNION ALL
SELECT 2,'2002-1-2' UNION ALL
SELECT 3,'2003-1-2' UNION ALL
SELECT 4,'2004-1-2' UNION ALL
SELECT 5,'1987-1-2' UNION ALL
SELECT 6,'1986-1-2' UNION ALL
SELECT 7,'1966-1-2' UNION ALL
SELECT 8,'1965-1-2' SELECT 年龄段='0-20',COUNT(*)AS 人数 FROM Employee
WHERE DATEDIFF(YY,BIRTH,GETDATE())>0 AND DATEDIFF(YY,BIRTH,GETDATE())<20UNION ALLSELECT 年龄段='20-30',COUNT(*)AS 人数 FROM Employee
WHERE DATEDIFF(YY,BIRTH,GETDATE())>=20 AND DATEDIFF(YY,BIRTH,GETDATE())<30UNION ALLSELECT 年龄段='30-40',COUNT(*)AS 人数 FROM Employee
WHERE DATEDIFF(YY,BIRTH,GETDATE())>=30 AND DATEDIFF(YY,BIRTH,GETDATE())<40UNION ALLSELECT 年龄段='大于40',COUNT(*)AS 人数 FROM Employee
WHERE DATEDIFF(YY,BIRTH,GETDATE())>=40年龄段 人数
------ -----------
0-20 4
20-30 2
30-40 0
大于40 2(所影响的行数为 4 行)
from table
where dateiff(yy,Birth,GETDATE()) between o and 20
group by Birth
union all
select count(Birth) num,Birth as age
from table
where dateiff(yy,Birth,GETDATE()) between 20 and 30
group by Birth
union all
select count(Birth) num,Birth as age
from table
where dateiff(yy,Birth,GETDATE()) between 30 and 40
group by Birth
AS
declare @icount int, @icount4 int,@JU varchar(20),@year int
BEGIN
select @icount =count(*) from Tb_yhxx where 1=1
set @year=year(getdate())
select @year - convert(int,substring(convert(varchar, isnull(a.chusrq,0),21),1,4)) as nianl into #ls_1
from Tb_yhxx a
select 6 as xh, convert(varchar(20),'20岁及以下 ') as nld ,count(*) as rs
into #ls_2 from #ls_1 where nianl<=20
insert into #ls_2(xh,nld, rs) select 5 as xh, '20岁到30岁' as nld ,count(*) as rs from #ls_1 where nianl<=30 and nianl>20
insert into #ls_2(xh,nld, rs) select 4 as xh,'30岁到40岁' as nld ,count(*) as rs from #ls_1 where nianl<=40 and nianl>30
insert into #ls_2(xh,nld, rs) select 3 as xh,'40岁到50岁' as nld ,count(*) as rs from #ls_1 where nianl<=50 and nianl>40
insert into #ls_2(xh,nld, rs) select 2 as xh,'50岁到60岁' as nld ,count(*) as rs from #ls_1 where nianl<=60 and nianl>50
insert into #ls_2(xh,nld, rs) select 1 as xh,'60岁以上' as nld ,count(*) as rs from #ls_1 where nianl<=100 and nianl>60
select @icount4 =sum(rs) from #ls_2
if(@icount -@icount4 >0)
BEGIN
insert #ls_2(xh, nld, rs ) values( 7,'年龄不详',@icount -@icount4 )
end
select * from #ls_2 order by xh
drop table #ls_1
drop table #ls_2
end
INSERT #Employee
SELECT 1,'2001-1-2' UNION ALL
SELECT 2,'2002-1-2' UNION ALL
SELECT 3,'2003-1-2' UNION ALL
SELECT 4,'2004-1-2' UNION ALL
SELECT 5,'1987-1-2' UNION ALL
SELECT 6,'1986-1-2' UNION ALL
SELECT 7,'1976-1-2' UNION ALL
SELECT 8,'1975-1-2'
select age,count(1)as qty
from
(select (case when datediff(yy,BIRTH,getdate()) between 0 and 20 then '0-20' when datediff(yy,BIRTH,getdate()) between 20 and 30 then '20-30' when datediff(yy,BIRTH,getdate()) between 30 and 40 then '30-40' end)as age
from #Employee)A
group by age/*
age qty
----- -----------
0-20 4
20-30 2
30-40 2(3 個資料列受到影響)*/drop table #Employee