帮我看看这个sql如何写

[fstao]

有两个表，分别是和t1和t2：
表t1
id f1 f2
1 A 3.0
2 B 4.0
3 A 5.0

表t2
id id1 f1 f2
1 1 2 3
2 1 3 4
3 1 3 5
4 2 2 2
5 2 4 2
6 3 5 1
7 3 6 2

表t1.id=表t2.id1，是主从关系，我想得到sum(t2.t1*t2.f2)+sum(t1.f2)的结果，比如：
f1 amount
A 41
B 16

因为t1.f1='a'时，amount=sum(t1.f2)+sum(t2.f1*t2.f2)=sum(3+5)+sum(2*3+3*4+3*5)=8+33=41，
t1.f1='b'时，amount=sum(t1.f2)+sum(t2.f1*t2.f2)=sum(4)+sum(4+8)=4+12=16

如何用sql语句实现呢？不要用游标。

[QQMagicer]

selec f1,(select sum(isnull(t2.t1,0)*isnull(t2.f2,0))+sum(isnull(t1.f2,0)) from t1,t2 where t1.id=t2.id1) as amount
from t1

[QQMagicer]

selec f1,(select sum(isnull(t2.t1,0)*isnull(t2.f2,0))+sum(isnull(t1.f2,0)) from t1,t2 where t1.id=t2.id1
and a.id = t2.id1
group by id1 ) as amount
from t1 as a

[子陌红尘]

create table #t1(id int,f1 char(1),f2 int)
insert into #t1 select 1,'A',3.0
insert into #t1 select 2,'B',4.0
insert into #t1 select 3,'A',5.0

create table #t2(id int,id1 int,f1 int,f2 int)
insert into #t2 select 1,1,2,3
insert into #t2 select 2,1,3,4
insert into #t2 select 3,1,3,5
insert into #t2 select 4,2,2,2
insert into #t2 select 5,2,4,2
insert into #t2 select 6,3,5,1
insert into #t2 select 7,3,6,2

select
a.f1,sum(b.f1*b.f2)+(select sum(f2) from #t1 where f1=a.f1)
from
#t1 a,#t2 b
where
a.id=b.id1
group by
a.f1

drop table #t1,#t2

[子陌红尘]

create table #t1(id int,f1 char(1),f2 int)
insert into #t1 select 1,'A',3.0
insert into #t1 select 2,'B',4.0
insert into #t1 select 3,'A',5.0

create table #t2(id int,id1 int,f1 int,f2 int)
insert into #t2 select 1,1,2,3
insert into #t2 select 2,1,3,4
insert into #t2 select 3,1,3,5
insert into #t2 select 4,2,2,2
insert into #t2 select 5,2,4,2
insert into #t2 select 6,3,5,1
insert into #t2 select 7,3,6,2

select
a.f1,sum(b.f1*b.f2)+(select sum(f2) from #t1 where f1=a.f1)
from
#t1 a,#t2 b
where
a.id=b.id1
group by
a.f1

drop table #t1,#t2

[fstao]

不对

[zlp321002]

表t1.id=表t2.id1 'A'有两个ID!

[ddxxx112]

--
select b.f1,a.f2+b.amount amount from (select f1,sum(f2) f2 from t1 group by f1) a,(select t1.f1,sum(t2.f1*t2.f2) amount from t1,t2 where t1.id=t2.id1 group by t1.f1) b where a.f1=b.f1

--结果
f1 amount
---- -----------
A 58
B 16

（所影响的行数为 2 行）

[$扫地僧$]

create table #t1(id int,f1 char(1),f2 int)
insert into #t1 select 1,'A',3.0
insert into #t1 select 2,'B',4.0
insert into #t1 select 3,'A',5.0

create table #t2(id int,id1 int,f1 int,f2 int)
insert into #t2 select 1,1,2,3
insert into #t2 select 2,1,3,4
insert into #t2 select 3,1,3,5
insert into #t2 select 4,2,2,2
insert into #t2 select 5,2,4,2
insert into #t2 select 6,3,5,1
insert into #t2 select 7,3,6,2


select t2.f1,t1.f12+t2.f2 as amount from
(select id1, sum(f1*f2) as f12 from #t2 group by id1) T1,
(select min(id) as id ,f1,sum(f2) as f2 from #t1 group by f1) T2
where T1.id1=t2.id

[zoubsky]

因为t1.f1='a'时，amount=sum(t1.f2)+sum(t2.f1*t2.f2)=sum(3+5)+sum(2*3+3*4+3*5)=8+33=41，

楼主，你的结果应当是错误的吧，因为t1.f1='a'时，对应的t1.id分别是1和3，所以
amount应当等于 sum(t1.f2)+sum(t2.f1*t2.f2)=sum(3+5)+sum(2*3+3*4+3*5+5*1+6*2) = 8+50=58
--测试代码
declare @tb1 table (id int,f1 char(1),f2 int)
insert @tb1
select 1,'A',3.0 union all
select 2,'B',4.0 union all
select 3,'A',5.0

declare @tb2 table(id int,id1 int,f1 int,f2 int)
insert @tb2
select 1,1,2,3 union all
select 2,1,3,4 union all
select 3,1,3,5 union all
select 4,2,2,2 union all
select 5,2,4,2 union all
select 6,3,5,1 union all
select 7,3,6,2

select a.f1,amount = sum(a.f2)+ sum(c.a2) from @tb1 as a left join
(select b.id1,sum(b.f1 * b.f2) as a2 from @tb2 as b group by b.id1)c
on a.id = c.id1 group by a.f1

/*
测试结果
f1 amount
---- -----------
A 58
B 16

（所影响的行数为 2 行）
*/

[zoubsky]

楼主，你的结果应当是错误的吧，
因为t1.f1='a'时，对应的t1.id分别是1和3，所以
amount应当等于 sum(t1.f2)+sum(t2.f1*t2.f2)=sum(3+5)+sum(2*3+3*4+3*5+5*1+6*2) = 8+50=58
而不是41



--测试代码
declare @tb1 table (id int,f1 char(1),f2 int)
insert @tb1
select 1,'A',3.0 union all
select 2,'B',4.0 union all
select 3,'A',5.0

declare @tb2 table(id int,id1 int,f1 int,f2 int)
insert @tb2
select 1,1,2,3 union all
select 2,1,3,4 union all
select 3,1,3,5 union all
select 4,2,2,2 union all
select 5,2,4,2 union all
select 6,3,5,1 union all
select 7,3,6,2

select a.f1,amount = sum(a.f2)+ sum(c.a2) from @tb1 as a left join
(select b.id1,sum(b.f1 * b.f2) as a2 from @tb2 as b group by b.id1)c
on a.id = c.id1 group by a.f1

/*
测试结果
f1 amount
---- -----------
A 58
B 16

（所影响的行数为 2 行）
*/