select * from tb结果:Title Count
AAA 1
BBB 2
CCC 3需要的结果Title Count 百分比
AAA 1 ( 1 / ( 1 + 2 + 3 )) (方法应该是这样吧,但是不知代码该如何书写)
BBB 2 ( 2 / ( 1 + 2 + 3 ))
CCC 3 ( 3 / ( 1 + 2 + 3 ))
AAA 1
BBB 2
CCC 3需要的结果Title Count 百分比
AAA 1 ( 1 / ( 1 + 2 + 3 )) (方法应该是这样吧,但是不知代码该如何书写)
BBB 2 ( 2 / ( 1 + 2 + 3 ))
CCC 3 ( 3 / ( 1 + 2 + 3 ))
Title,
Count,
百分比=ltrim(cast(count*100.0/s.sc as dec(18,2))+'%'
from tb t,(select sum(count) as sc from tb) s
go
create table [tb]([Title] varchar(3),[Count] int)
insert [tb]
select 'AAA',1 union all
select 'BBB',2 union all
select 'CCC',3select
t.Title,
t.[Count],
百分比=ltrim(cast(t.[count]*100.0/s.sc as dec(18,2)))+'%'
from tb as t,(select sum([count]) as sc from tb) as s
--测试结果:
/*
Title Count 百分比
----- ----------- -----------------------------------------
AAA 1 16.67%
BBB 2 33.33%
CCC 3 50.00%(所影响的行数为 3 行)*/
go
create table [tb]([Title] varchar(3),[Count] int)
insert [tb]
select 'AAA',1 union all
select 'BBB',2 union all
select 'CCC',3select
Title,
[Count],
百分比=left(round([count]*100.0/(select sum([count]) from tb),2),5)+'%'
from tb--测试结果:
/*
Title Count 百分比
----- ----------- -----------------------------------------
AAA 1 16.67%
BBB 2 33.33%
CCC 3 50.00%(所影响的行数为 3 行)
insert tb
select 'AAA',1 union all
select 'BBB',2 union all
select 'CCC',3select * , 百分比 = cast([Count]*100.0/(select sum([Count]) from tb) as decimal(18,2)) from tbdrop table tb/*
Title Count 百分比
----- ----------- --------------------
AAA 1 16.67
BBB 2 33.33
CCC 3 50.00(所影响的行数为 3 行)
*/
insert tb
select 'AAA',1 union all
select 'BBB',2 union all
select 'CCC',3--方法1
select * , 百分比 = cast([Count]*100.0/(select sum([Count]) from tb) as decimal(18,2)) from tb--方法2
select m.* , 百分比 = cast(m.[Count]*100.0/n.[count] as decimal(18,2))
from tb m , (select sum([count]) [count] from tb) ndrop table tb/*
Title Count 百分比
----- ----------- --------------------
AAA 1 16.67
BBB 2 33.33
CCC 3 50.00(所影响的行数为 3 行)
*/
(
Title nvarchar(30),
count1 int
)
insert into #T
select 'AAA', 1 union all
select 'BBB', 2 union all
select 'CCC', 3
select Title,count1,count1*1.0/B*1.0 from #T,(select sum(count1) B from #T) A
drop table #T
将数据类型 varchar 转换为 numeric 时出错。
if object_id('tb') is not null
drop table tb
go
create table tb(title varchar(100),[count] int)
go
insert into tb
select 'AAA',1 union all
select 'BBB',2 union all
select 'CCC',3
go
select title,[count],百分比=cast(cast(cast([count]/((select count(*) from tb)*1.0) as numeric(10,2))*100 as int) as varchar)+'%' from tb