分类求和问题!!

[loulanlouzhu]

表1:
aaa bbb ccc
a1 b1 2
a1 b2 3
a2 b1 4
a2 b2 5

想得到:

ddd fff ggg
a1 b1 2/(2+3)
a1 b2 3/(2+3)
a2 b1 4/(4+5)
a2 b2 5/(4+5)

感谢帮助@

[aierong]

create table ai(aaa varchar(10), bbb varchar(10), xxx varchar(10), ccc real)
insert into ai select 'a1', 'b1', 'x1', 1
insert into ai select 'a1' , 'b1', 'x2' , 2
insert into ai select 'a1' , 'b2', 'x1' , 3
insert into ai select 'a1' , 'b2', 'x2' , 4
insert into ai select 'a2' ,'b1', 'x1' , 5
insert into ai select 'a2' , 'b1', 'x2' , 6
insert into ai select 'a2' , 'b2', 'x1' , 7
insert into ai select 'a2' , 'b2' , 'x2' , 8

ddd fff ggg
a1 b1 (1+2)/(1+2+3+4)
a1 b2 (3+4)/(1+2+3+4)
a2 b1 (5+6)/(5+6+7+8)
a2 b2 (7+8)/(5+6+7+8)

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

[aierong]

没有时间啦
要吃饭去啦
答案给你啦，满意不

[loulanlouzhu]

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 语句里是就不行了!说返回值只能是一个!

[loulanlouzhu]

感谢各位!

[zhbname]

up

[pengdali]

create table #dali(aaa varchar(10), bbb varchar(10), xxx varchar(10), ccc int)
insert #dali select 'a1', 'b1', 'x1', 1
insert #dali select 'a1' , 'b1', 'x2' , 2
insert #dali select 'a1' , 'b2', 'x1' , 3
insert #dali select 'a1' , 'b2', 'x2' , 4
insert #dali select 'a2' ,'b1', 'x1' , 5
insert #dali select 'a2' , 'b1', 'x2' , 6
insert #dali select 'a2' , 'b2', 'x1' , 7
insert #dali select 'a2' , 'b2' , 'x2' , 8


select aaa ddd,bbb as fff,cast(sum(ccc)/(select sum(ccc)+0.0 from #dali where a.aaa=aaa) as numeric(10,2)) as ggg from #dali a
group by aaa,bbb


go

drop table #dali

[loulanlouzhu]

谢谢各位兄弟!!

找到原因了!

[loulanlouzhu]

真是差之毫厘,谬以千里!

[Dennis618]

create table #表(aaa varchar(10), bbb varchar(10), xxx varchar(10), ccc int)
insert #表 select 'a1', 'b1', 'x1', 1
insert #表 select 'a1' , 'b1', 'x2' , 2
insert #表 select 'a1' , 'b2', 'x1' , 3
insert #表 select 'a1' , 'b2', 'x2' , 4
insert #表 select 'a2' ,'b1', 'x1' , 5
insert #表 select 'a2' , 'b1', 'x2' , 6
insert #表 select 'a2' , 'b2', 'x1' , 7
insert #表 select 'a2' , 'b2' , 'x2' , 8
select aaa,bbb,
(select sum(ccc)+0.0 from #表 b where a.aaa=b.aaa and a.bbb=b.bbb group by aaa,bbb)/(select sum(ccc)+0.0 from #表 c where a.aaa=c.aaa group by aaa) from #表 a group by a.aaa,a.bbb
drop table #表

[yujohny]

select a.ddd,a.bbb as fff ,a.ccc/(select sum(ccc) from 表1 where ddd=a.ddd)
from 表1 a

[伍子V5]

select a.aaa as ddd,a.bbb as fff ,a.ccc/(select sum(ccc) from 表1 where aaa=a.aaa) as ggg from 表1 a

[loulanlouzhu]

没看明白!!

现在只有一个表1,下面是我想得到的结果集!!


a.ddd是啥意思!?

[dafu71]

select aaa as ddd,bbb as fff, ccc/(select sum(ccc) from 表一 where
aaa=别名.aaa) as ggg from 表一 as 别名

[aierong]

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

[aierong]

a.ddd是用了别名
给表起别名

[123456754321]

别名

[pengdali]

精确到两位小数:


select aaa,bbb,cast(ccc/(select sum(ccc)+0.0 from 表1 where aaa=a.aaa) as numeric(10,2)) from 表1 a

[aierong]

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

[aierong]

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

[loulanlouzhu]

如下情况呢!?

表1:
aaa bbb xxx ccc
a1 b1 x1 1
a1 b1 x2 2
a1 b2 x1 3
a1 b2 x2 4
a2 b1 x1 5
a2 b1 x2 6
a2 b2 x1 7
a2 b2 x2 8

想得到:

ddd fff ggg
a1 b1 (1+2)/(1+2+3+4)
a1 b2 (3+4)/(1+2+3+4)
a2 b1 (5+6)/(5+6+7+8)
a2 b2 (7+8)/(5+6+7+8)

感谢帮助@