SELECT PName, COUNT(1)*1./(select count(1) from tb) AS T FROM TB GROUP BY PNAME
SELECT PName, CAST(COUNT(1)*1.0/(select count(1) from tb) AS DEC(18,0))AS T FROM TB GROUP BY PNAME
--测试数据 create table #a ( pid int ,pname varchar(10) ) insert into #a select 1,'aa' union all select 2,'aa' union all select 3,'bb' union all select 4,'aa' union all select 5 ,'bb' --表达式 declare @sql varchar(800) set @sql ='select pname ,convert(varchar(10),cast(count(1)*100/(select count(1)from #a) as decimal(18,2)))+''%'' as per from #a group by pname' print @sql exec(@sql) --结果: pname per ---------- ----------- aa 60.00% bb 40.00%
--测试数据 drop table #a create table #a ( pid int ,pname varchar(10) ) insert into #a select 1,'aa' union all select 2,'aa' union all select 3,'bb' union all select 4,'aa' union all select 5 ,'bb' union all select 6,'cc'SELECT PName, CAST(COUNT(1)*1.0/(select count(1) from #a) AS DEC(18,0))AS T FROM #a GROUP BY PNAME测试数据 + 小F的 也不行
select pname, cast(((select count(pname) from test_two t1 where t1.pname=t.pname)+ 0.00)/(select count(1) from test_two t2) as numeric(3,2)) from test_two t group by pname
SELECT PName, CAST(COUNT(1)*1.0/(select count(1) from tb) AS DEC(18,0))AS T FROM TB GROUP BY PNAME
PName,
COUNT(1)*1./(select count(1) from tb) AS T
FROM TB
GROUP BY PNAME
PName,
CAST(COUNT(1)*1.0/(select count(1) from tb) AS DEC(18,0))AS T
FROM TB
GROUP BY PNAME
--测试数据
create table #a
(
pid int
,pname varchar(10)
)
insert into #a
select 1,'aa' union all
select 2,'aa' union all
select 3,'bb' union all
select 4,'aa' union all
select 5 ,'bb' --表达式
declare @sql varchar(800)
set @sql ='select pname ,convert(varchar(10),cast(count(1)*100/(select count(1)from #a) as decimal(18,2)))+''%'' as per from #a group by pname'
print @sql
exec(@sql)
--结果:
pname per
---------- -----------
aa 60.00%
bb 40.00%
drop table #a
create table #a
(
pid int
,pname varchar(10)
)
insert into #a
select 1,'aa' union all
select 2,'aa' union all
select 3,'bb' union all
select 4,'aa' union all
select 5 ,'bb' union all
select 6,'cc'SELECT
PName,
CAST(COUNT(1)*1.0/(select count(1) from #a) AS DEC(18,0))AS T
FROM #a
GROUP BY PNAME测试数据 + 小F的 也不行
pname,
cast(((select count(pname) from test_two t1 where t1.pname=t.pname)+ 0.00)/(select count(1) from test_two t2)
as numeric(3,2))
from
test_two t
group by
pname
PName,
CAST(COUNT(1)*1.0/(select count(1) from tb) AS DEC(18,0))AS T
FROM TB
GROUP BY PNAME