SQL求百分比解决思路

SQL求百分比
数据如下 

HTML code

pid     pname 1       AA 2       AA 3       BB 4       AA 5       BB

求AA和BB占的百分比

HTML code

Pname   perAA      60%BB      40%

 
请教SQL语句怎么写？

[解决办法]
SELECT 
PName,
COUNT(1)*1./(select count(1) from tb) AS T
FROM TB
GROUP BY PNAME
[解决办法]

SQL code

SELECT    PName,  CAST(COUNT(1)*1.0/(select count(1) from tb) AS DEC(18,0))AS TFROM TBGROUP BY PNAME
[解决办法]
探讨

SQL code
SELECT  
  PName,
  CAST(COUNT(1)*1.0/(select count(1) from tb) AS DEC(18,0))AS T
FROM TB
GROUP BY PNAME

[解决办法]
SQL code
--测试数据create table #a (    pid int ,pname varchar(10))insert into #a select 1,'aa' union allselect 2,'aa' union allselect 3,'bb' union allselect 4,'aa' union allselect 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 @sqlexec(@sql)--结果：pname      per---------- -----------aa         60.00%bb         40.00% 

 

查看更多 下一篇