create table 表1(aaa varchar(100), bbb varchar(100), ccc int)
insert into 表1 select 'a1', 'b1', 2
union all select 'a1' , 'b2', 3
union all select 'a2', 'b1', 4
union all select 'a2', 'b2', 5
select aaa as ddd,bbb as fff,convert(real,ccc/(select sum(ccc)+0.000 from 表1 where a.aaa=aaa)) as ggg
from 表1 as a
create table 表1(aaa varchar(100), bbb varchar(100), ccc int)
insert into 表1 select 'a1', 'b1', 2
union all select 'a1' , 'b2', 3
union all select 'a2', 'b1', 4
union all select 'a2', 'b2', 5
go
select a.aaa as ddd,a.bbb as fff,a.ccc/(select sum(ccc) from 表1 where a.aaa=aaa) as ggg
from (
select aaa,bbb,ccc*1.0000 as ccc
from 表1) as a
create table 表1(aaa varchar(100), bbb varchar(100), ccc real)
insert into 表1 select 'a1', 'b1', 2
union all select 'a1' , 'b2', 3
union all select 'a2', 'b1', 4
union all select 'a2', 'b2', 5
--记得ccc列不可以为整形,要不结果就是0
select aaa as ddd,bbb as fff,ccc/(select sum(ccc) from 表1 where a.aaa=aaa)
from 表1 as a
select aaa,bbb,cast(ccc/(select sum(ccc)+0.0 from 表1 where aaa=a.aaa) as numeric(10,2)) from 表1 a
-->这一个还没搞定!反不起来!
select sum(ccc)+0.0 from 表1 where aaa=a.aaa,这个返回的是2+3+4+5的值,并没有将aaa列分开!
select sum(ccc)+0.0 from 表1 where aaa=a.aaa group by aaa -->这样写的话才是我要的结果!,但是放到整个select aaa,bbb,cast(ccc/(select sum(ccc)+0.0 from 表1 where aaa=a.aaa group by aaa ) as numeric(10,2)) from 表1 a 语句里是就不行了!说返回值只能是一个!
select aaa as ddd,
bbb as fff,
(select sum(ccc) from ai where a.aaa=aaa and a.bbb=bbb) /(select sum(ccc) from ai where a.aaa=aaa) as ggg
from ai as a
group by aaa,bbb