34,837
社区成员




--小数保留位的处理
--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)
--可以动态控制位数,但要循环
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 行)
*/
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 行受影响)
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 行受影响)
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
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 行)
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%
*/
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