如何从出生年月中统计出年龄,并计算出占总数的百分比?

[powbcom]

CREATE TABLE #Test(id int, birth datetime)

INSERT INTO #Test select 1, '1978-10-12'
union all select 2, '1981-5-21'
union all select 3, '1975-12-3'
union all select 4, '1980-10-12'

如何从出生年月中统计出年龄,并计算出占总数的百分比?

例
年龄 百分比 数量
26 25% 1
27 25% 1

[子陌红尘]

CREATE TABLE #Test(id int, birth datetime)

INSERT INTO #Test select 1, '1978-10-12'
union all select 2, '1981-5-21'
union all select 3, '1975-12-3'
union all select 4, '1980-10-12'


select
datediff(yy,birth,getdate()) as age,
rtrim(count(*)*100/(select count(1) from #Test))+'%' as [percent],
count(*) as num
from #Test
group by datediff(yy,birth,getdate())


drop table #Test

[huailairen]

select datediff(yy,birth,getdate()),cast(ceiling(count(*)*1.0/(select count(*) from #test )*100) as varchar(3))+'%',count(*)
from #test
group by birth
order by datediff(yy,birth,getdate())

[huailairen]

select datediff(yy,birth,getdate()) as 年龄,cast(ceiling(count(*)*1.0/(select count(*) from #test )*100) as varchar(3))+'%' as 百分比,count(*) as 数量
from #test
group by birth
order by datediff(yy,birth,getdate())

结果
年龄 百分比 数量
----------- ---- -----------
25 25% 1
26 25% 1
28 25% 1
31 25% 1

（所影响的行数为 4 行）

[撸大湿]

CREATE TABLE #Test(id int, birth datetime)

INSERT INTO #Test select 1, '1978-10-12'
union all select 2, '1981-5-21'
union all select 3, '1975-12-3'
union all select 4, '1980-10-12'


--年龄 百分比 数量
--26 25% 1
--27 25% 1


select count(1) from #test
select datediff( yy,birth,getdate()) 年龄
,convert(nvarchar(100),convert (float,count(year(birth)))/(select count(1) from #test )*100)+'%'比例
,count(year(birth)) 数量
from #test group by datediff( yy,birth,getdate())
drop table #test

[tian790317]

select '年龄'=datediff(yy,birth,getdate()),count(datediff(yy,birth,getdate())), count(*)*1.0/(select count(*) from #test ) from #test group by datediff(yy,birth,getdate())

[aniude]

CREATE TABLE #Test(id int, birth datetime)

INSERT INTO #Test select 1, '1978-10-12'
union all select 2, '1981-5-21'
union all select 3, '1975-12-3'
union all select 4, '1980-10-12'


select 年龄=datediff(day,birth,getdate()),
百分比=cast(count(2)*100/(select count(1) from #Test) as varchar(10))+'%',
数量=count(1)
from #Test group by datediff(day,birth,getdate())

[aniude]

刚刚去吃饭了，没看，居然结贴了