求SQL语句

[jlty7390]

数据库：
create table tb(ID int,等级 varchar(10))
insert into tb values(1,'1')
insert into tb values(1,'1')
insert into tb values(1,'2')
insert into tb values(1,'3')
insert into tb values(1,'3')
insert into tb values(1,'2')
insert into tb values(2,'1')
insert into tb values(2,'2')
insert into tb values(2,'3')
insert into tb values(2,'3')
insert into tb values(2,'2')
insert into tb values(3,'1')
insert into tb values(3,'1')
insert into tb values(3,'2')
insert into tb values(3,'3')
insert into tb values(3,'3')
insert into tb values(3,'2')

go

用一条 SQL语句求出分类各ID'等级'为'1'所占百分比

类似如下结果

1 33。33%
2 25%
3 33。33%

[fcuandy]

 create       table       tb(ID       int,等级       varchar(10))   
insert       into       tb       values(1,'1')       
insert       into       tb       values(1,'1')       
insert       into       tb       values(1,'2')       
insert       into       tb       values(1,'3')       
insert       into       tb       values(1,'3')       
insert       into       tb       values(1,'2')   
insert       into       tb       values(2,'1')       
insert       into       tb       values(2,'2')       
insert       into       tb       values(2,'3')       
insert       into       tb       values(2,'3')       
insert       into       tb       values(2,'2')   
insert       into       tb       values(3,'1')       
insert       into       tb       values(3,'1')       
insert       into       tb       values(3,'2')       
insert       into       tb       values(3,'3')       
insert       into       tb       values(3,'3')       
insert       into       tb       values(3,'2')   

go   
select id,rtrim(cast(100.00*count(case when 等级='1' then 1 else null end)/count(*) as numeric(10,2))) + '%' 比例 from tb group by id
select id,rtrim(cast(100.00*sum(case when 等级='1' then 1 else 0 end)/count(*) as numeric(10,2))) + '%' 比例 from tb group by id
go
drop table tb

go

• 打赏
• 举报

回复 赞

[chuifengde]

declare @a table(ID       int,等级       varchar(10))   
insert       into       @a       values(1,'1')       
insert       into       @a       values(1,'1')       
insert       into       @a       values(1,'2')       
insert       into       @a       values(1,'3')       
insert       into       @a       values(1,'3')       
insert       into       @a       values(1,'2')   
insert       into       @a       values(2,'1')       
insert       into       @a       values(2,'2')       
insert       into       @a       values(2,'3')       
insert       into       @a       values(2,'3')       
insert       into       @a       values(2,'2')   
insert       into       @a       values(3,'1')       
insert       into       @a       values(3,'1')       
insert       into       @a       values(3,'2')       
insert       into       @a       values(3,'3')       
insert       into       @a       values(3,'3')       
insert       into       @a       values(3,'2')   

select id,str(sum(case when 等级='1' then 1 else 0 end)*100.0/count(等级),20,2)+'%' x from @a group by id
--result
/*
id          x                     
----------- --------------------- 
1                          33.33%
2                          20.00%
3                          33.33%
*/

• 打赏
• 举报

回复 赞

[中国风]

use test
go
  create       table       tb(ID       int,等级       varchar(10))   
insert       into       tb       values(1,'1')       
insert       into       tb       values(1,'1')       
insert       into       tb       values(1,'2')       
insert       into       tb       values(1,'3')       
insert       into       tb       values(1,'3')       
insert       into       tb       values(1,'2')   
insert       into       tb       values(2,'1')       
insert       into       tb       values(2,'2')       
insert       into       tb       values(2,'3')       
insert       into       tb       values(2,'3')       
insert       into       tb       values(2,'2')   
insert       into       tb       values(3,'1')       
insert       into       tb       values(3,'1')       
insert       into       tb       values(3,'2')       
insert       into       tb       values(3,'3')       
insert       into       tb       values(3,'3')       
insert       into       tb       values(3,'2')   

go

select
	ID,
	[百分率]=rtrim(cast(sum(case when 等级=1 then 1 else 0 end)*1.0/(select count(1) from tb where ID=a.ID)*100 as decimal(18,2)))+'%'
from 
	Tb a
group by ID


ID          百分率                                       
----------- ----------------------------------------- 
1           33.33%
2           20.00%
3           33.33%

（所影响的行数为 3 行）

• 打赏
• 举报

回复 赞

[dawugui]

http://topic.csdn.net/u/20071211/11/d7b56535-9516-429a-9e41-7a045edc0309.html

在这里已经写了.

• 打赏
• 举报

回复 赞

[晓风残月0110]

select id,rtrim(cast(round(100.00*count(case when 等级='1' then 1 else null end)/count(1),2) as decimal(10,2))) + '%' 比例 from tb group by id

• 打赏
• 举报

回复 赞

[kk19840210]

select a.id,convert(varchar(100),a.a*1.0/b.b*100)+'%' from
(select id,a=count(1) from tb where 等级='1' group by id )a,
(select id,b=count(1) from tb group by id)b
where a.id=b.id



id          
----------- -----------------------------------------------------------------------------------------------------
1           33.333333333300%
2           20.000000000000%
3           33.333333333300%

(3 行受影响)

• 打赏
• 举报

回复 赞

[kk19840210]

select a.id,left(convert(varchar(100),a.a*1.0/b.b*100),5)+'%' from
(select id,a=count(1) from tb where 等级='1' group by id )a,
(select id,b=count(1) from tb group by id)b
where a.id=b.id

id          
----------- -----------
1           33.33%
2           20.00%
3           33.33%

(3 行受影响)

• 打赏
• 举报

回复 赞

[dawugui]

--不好意思,看错了.

create       table       tb(ID       int,等级       varchar(10))   
insert       into       tb       values(1,'1')       
insert       into       tb       values(1,'1')       
insert       into       tb       values(1,'2')       
insert       into       tb       values(1,'3')       
insert       into       tb       values(1,'3')       
insert       into       tb       values(1,'2')   
insert       into       tb       values(2,'1')       
insert       into       tb       values(2,'2')       
insert       into       tb       values(2,'3')       
insert       into       tb       values(2,'3')       
insert       into       tb       values(2,'2')   
insert       into       tb       values(3,'1')       
insert       into       tb       values(3,'1')       
insert       into       tb       values(3,'2')       
insert       into       tb       values(3,'3')       
insert       into       tb       values(3,'3')       
insert       into       tb       values(3,'2')   

go   
select id , 所占百分比 = cast(cast(count(*)*1.0*100/(select count(*) from tb where id = a.id) as decimal(18,2)) as varchar) + '%'  from tb a where 等级 = 1 group by id

drop table tb

/*
id          所占百分比                           
----------- ------------------------------- 
1           33.33%
2           20.00%
3           33.33%

（所影响的行数为 3 行）

*/

• 打赏
• 举报

回复 赞

[晓风残月0110]

--小数保留位的处理
--1
declare @yourFloat float
set @yourFloat = 0.111
select rtrim(cast(@yourFloat * 100 as decimal(10,2))) + '%'
--11.10%

--2
select cast(round(cast(1.0 * 1/3000 * 100 as decimal(10,2)),2) as varchar) + '%'
--0.03%
--使用decimal控制小数的位数，不能控制0的情况如25.00%
--3
declare @str nvarchar(20), @len int
set @str = '0.1200'
set @len = charindex('.',@str)+1
while charindex('0',@str,@len)=@len
begin
    set @len=@len+1
end
select substring(@str,1,@len+1)
--可以动态控制位数，但要循环

• 打赏
• 举报

回复 赞

[areswang]

select a.id,rtrim(cast((a.cnt*100/b.cnnt) as numeric(9,2)))+'%' 百分比
from (select id,cast(sum(id) as numeric(9,4)) cnt from tb
where 等级=1
group by id,等级) a
left join (select id,cast(sum(id) as numeric(9,4)) cnnt
from tb group by id) b
on a.id = b.id

• 打赏
• 举报

回复 赞

[jlty7390]

牛人 多 :)

• 打赏
• 举报

回复 赞

[晓风残月0110]

建议根据你的数据，猜测可能的小数位，
然后decimal(10,10)给定合适的位数

• 打赏
• 举报

回复 赞

[jlty7390]

测试下结帖

• 打赏
• 举报

回复 赞

[andysun88]

select id,per = convert(varchar,convert(float,round(sum(Convert(int,等级)) *100.0/(select sum(Convert(int,等级)) from tb),2),2))+'%'
from tb
group by id

• 打赏
• 举报

回复 赞