求一sql语句select出列的百分比

[gonguui1982]

表A
ID Name num
1 小明 23
2 小刚 33
3 小红 44

求每个人num的数量占总数量的百分比
查询结果

name per
小明 0.23
小刚 0.33
小红 0.44

[pard521]

select id,Name,num=cast(num*1.0/(select SUM(num) from 表A) as decimal(28,2)) from 表A

[骑着蜗牛去爬山]

select id,Name,num=cast(num*1.0/(select SUM(num) from tb) as decimal(28,2)) from tb

[中国风]

use Tempdb
go
--> --> 
 
declare @A table([ID] int,[Name] nvarchar(2),[num] int)
Insert @A
select 1,N'小明',23 union all
select 2,N'小刚',33 union all
select 3,N'小红',44
 
Select a.[Name],cast(a.[num]*1.0/b.[num] as numeric(8,2)) as per 
from @A as a,(select sum([num]) as [num] from @A) as b
/*
Name	per
小明	0.23
小刚	0.33
小红	0.44
/

[中国风]

或

declare @A table([ID] int,[Name] nvarchar(2),[num] int)
Insert @A
select 1,N'小明',23 union all
select 2,N'小刚',33 union all
select 3,N'小红',44
 
Select a.[Name],cast(a.[num]*1.0/sum(a.[num])over() as numeric(8,2)) as per 
from @A as a
/*
Name	per
小明	0.23
小刚	0.33
小红	0.44
*/

[中国风]

sql05以上版本用3楼方法效率比较高